What is this array formula doing? Is there a better way to write it? Please explain in detail.
=MAX(IF('[Daily sales_2013_v78.xlsx]data2013'!$E$8:$NE$8='fcast-closings'!C$4,'[Daily sales_2013_v78.xlsx]data2013'!$E91:$NE91,""))
What is this array formula doing? Is there a better way to write it? Please explain in detail.
=MAX(IF('[Daily sales_2013_v78.xlsx]data2013'!$E$8:$NE$8='fcast-closings'!C$4,'[Daily sales_2013_v78.xlsx]data2013'!$E91:$NE91,""))
The IF Formula:
[data2013]E8 = [fcast-closings]C4, if true, [data2013]E91, if false, blank
[data2013]F8 = [fcast-closings]C4, if true, [data2013]F91, if false, blank
[data2013]G8 = [fcast-closings]C4, if true, [data2013]G91, if false, blank
...
[data2013]NE8 = [fcast-closings]C4, if true, [data2013]NE91, if false, blank
Once it has evaluated all the cells above, find the MAX value of the array.
It returns the maximum value from range data2013'!$E91:$NE91
for criteria specified in data2013'!$E$8:$NE$8.
So let say for
A B A B A A
1 3 4 1 2 3
It will return 4 for "A"
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thank you. Is there a more effective formula to use? That formula is annoying.
Another option for MAX calculation with array formula:
=MAX(('[Daily sales_2013_v78.xlsx]data2013'!$E$8:$NE$8='fcast-closings'!C$4)*'[Daily sales_2013_v78.xlsx]data2013'!$E91:$NE91)
And with non-array formula:
=MAX(INDEX(('[Daily sales_2013_v78.xlsx]data2013'!$E$8:$NE$8='fcast-closings'!C$4)*'[Daily sales_2013_v78.xlsx]data2013'!$E91:$NE91,))
Quang PT
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks