Help with arrary formula

[ammartino44]

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,""))

[lazyme]

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.

[RobertMika]

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"

[ammartino44]

Thank you. Is there a more effective formula to use? That formula is annoying.

[Tony Valko]

Is there a more effective formula to use?

Not really. That formula is very succinct and uses as few "moving parts" as is possible to perform the task.

[bebo021999]

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,))

[AliGW]

Thank you. Is there a more effective formula to use? That formula is annoying.

Out of interest, what is it about that formula that is 'annoying'?