Excel 2007 : average last 5 values meeting certain criteria

**jrbdotcom31** · 05-14-2010, 12:32 PM

Hi,
I am using the following formula
=AVERAGEIF(D$2:D447, D448, K$2:K447)
which gives me the average of all the values from k2 to k447 that meet certain criteria. Is there a way to get just the average of the last 5 entries that meet that same criteria rather than all entries.

Jared

**NBVC** · 05-14-2010, 12:41 PM

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER.

**jrbdotcom31** · 05-14-2010, 04:17 PM

Originally Posted by NBVC

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER.

Thank you. However, for some reason, it is not giving my the correct value that I have computed manually. Can you explain the Row(D$2)+1 part so that I can understand the code better and try to make it work. Thanks in advance.

jared

**NBVC** · 05-14-2010, 09:36 PM

You are correct, the formula should be more like:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.

the -ROW(D2)+1 basically resets the array created by ROW(D2:D447) to start from 1.. So Row(D2) is subtracted from each element in ROW(D2:D447) to give an array like {0,1,2,3,....etc} and then 1 is added to each, so that array begins at 1...

Then, each Row number replaces the TRUE results of the D2:D447=D448 condition.. and the large() finds the kth largest row number and extract the reference at that point.... This is repeated to find the largest (or last time the condition is true) and a range is found.. then within that range, the AVERAGE(IF()) is used to get the average of the elements in K where D is equal to D448.

**jrbdotcom31** · 05-15-2010, 11:47 PM

Originally Posted by NBVC

You are correct, the formula should be more like:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.

the -ROW(D2)+1 basically resets the array created by ROW(D2:D447) to start from 1.. So Row(D2) is subtracted from each element in ROW(D2:D447) to give an array like {0,1,2,3,....etc} and then 1 is added to each, so that array begins at 1...

Then, each Row number replaces the TRUE results of the D2:D447=D448 condition.. and the large() finds the kth largest row number and extract the reference at that point.... This is repeated to find the largest (or last time the condition is true) and a range is found.. then within that range, the AVERAGE(IF()) is used to get the average of the elements in K where D is equal to D448.

Thank You so much! It worked!!! I tried several ideas, but couldn't find the right approach. By the looks of it, I probably never would have. Thanks again for the formula and the explanation so that I can learn more as well.