- Joined
- Sep 29, 2016
- Messages
- 3
- Reaction score
- 0
I'm currently using an array formula in excel and I've learned that Numbers doesn't work with Arrays. I was looking for some help on translating into something that would work in Numbers.
=INDEX(A23:K23, MATCH(MAX(IF(MOD(COLUMN(B23:L23)-COLUMN(B23)+1,2)=1,B42:L42-ABS(B41:L41-T17)/10^6)), IF(MOD(COLUMN(B23:L23)-COLUMN(B23)+1,2)=1,B42:L42-ABS(B41:L41-T17)/10^6),0))
What this formula is doing is first finding which total score is the highest. Located in Row 20 & 41 =(9) Then if there is multiple or tie score it will then take the bold number(Row 19 & 40) and see which of those numbers is closest to the tiebreaker number which is located in T17. The formula is keyed into N42 and will put the name of the person who wins the week.
As you can tell the formula only works with the bottom half. I learned in excel since they are top to bottom I can't have the formula check all the data, only the data in a given Row. I may reorganize the sheet if the same thing applies in numbers. For now I'm hoping to keep it as is.
El Capitan 10.11.6
Numbers 3.6.2
Excel 2011
ETA: I've tried to copy/paste the formula and also open the excel file to numbers
=INDEX(A23:K23, MATCH(MAX(IF(MOD(COLUMN(B23:L23)-COLUMN(B23)+1,2)=1,B42:L42-ABS(B41:L41-T17)/10^6)), IF(MOD(COLUMN(B23:L23)-COLUMN(B23)+1,2)=1,B42:L42-ABS(B41:L41-T17)/10^6),0))
What this formula is doing is first finding which total score is the highest. Located in Row 20 & 41 =(9) Then if there is multiple or tie score it will then take the bold number(Row 19 & 40) and see which of those numbers is closest to the tiebreaker number which is located in T17. The formula is keyed into N42 and will put the name of the person who wins the week.
As you can tell the formula only works with the bottom half. I learned in excel since they are top to bottom I can't have the formula check all the data, only the data in a given Row. I may reorganize the sheet if the same thing applies in numbers. For now I'm hoping to keep it as is.
El Capitan 10.11.6
Numbers 3.6.2
Excel 2011
ETA: I've tried to copy/paste the formula and also open the excel file to numbers
Last edited: