Array to Calc Bottom Average

try this

Formula:

Please Login or Register  to view this content.

If you use the If statement like I did it returns False when <>. The way you did the LARGE, when it multiplies the False by a number it interprets the False as a 0 (True = 1). 0 is a small number so this is why you only noticed when using the Small function and not the Large.

In excel 2007 you can make a pivot table of it.

Then there is a option to calculate the top 3.

Yould take a avarage of it.

I don't have excel 2007 at the moment.

I will look for it this evening, and respond back.

Originally Posted by Hawkeye16

try this

Formula:

Please Login or Register  to view this content.

If you use the If statement like I did it returns False when <>. The way you did the LARGE, when it multiplies the False by a number it interprets the False as a 0 (True = 1). 0 is a small number so this is why you only noticed when using the Small function and not the Large.

This is a good solution, though I've got the feeling that this syntax for array constants doesn't work with systems where the decimal separator is ".".

It should, since then we'd have a universal system, but I don't believe it does, so should be:

=AVERAGE(SMALL(IF($A$2:$A$46=E14,$B$2:$B$46),{1,2,3}))

Regards

Originally Posted by Hawkeye16

Yeah - So much changes I figured I would attach a workbook, excel seems to correct itself. That is good to know for the future though. Thanks XOR LX!

No worries! I've been on the other end of this when an OP has come back to me saying that there was an error in the formula (containing e.g. {1,2,3}), not knowing for the life of me what it was until someone pointed out this alternative syntax with "\".

Still not sure why Microsoft don't just standardise this part of Excel...

Cheers

Please Login or Register  to view this content.

in #3.

I didn't found the solution with pivot table.