Same formula returns 0 in some cells, works correctly in others

Here are 2 approaches:

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
returns the average of the 5 lowest Col_C values where the corresponding Col_A value matches E2

Please Login or Register  to view this content.

...and this regular (but longer) formula does the same thing (without needing CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

Is that something you can work with?

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

Ok I thought it had worked but upon further inspection, some is off. If you look at the attached picture, the equations use returned top 3 and top 5 averages that are higher than the Top 1 value which is impossible. The equations used are as follows:

Top1: =MAX(IF(($L$2:$L$34=$N$4),$K$2:$K$34))
Top3: =AVERAGE(LARGE(INDEX(($L$2:$L$23<>$N$4)*$K$2:$K$23,0),{1,2,3}))
Top5: =AVERAGE(LARGE(INDEX(($L$2:$L$23<>$N$4)*$K$2:$K$23,0),{1,2,3,4,5}))

The "TRUE" results are what I manually selected and calculated so they would be correct.

(The MIN version which I asked about in the OP is exhibiting the same behavior)

Edit: I see that the MAX formula uses an "=" condition and the AVERAGE formulas use a "<>" condition It appears that is the issue. What would the "<>" be useful for?

Excel2.png

1st, not all members can see png pics in here, plus, we cannot work with pics anyway

2nd "<>" means "not equal to"

3rd, note that those are ARRAY formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

(I suspect that 3 above, is probably your problem)