Average of last three non-zero, but one i a column

[RIZVI]

Hi All,

I am trying to get average of last three non zero entries but one, through this formula: {=AVERAGE(IF(ROW(Q5:Q55)>=LARGE(IF(Q5:Q55,ROW(Q5:Q55)),3),IF(Q5:Q55,Q5:Q55)))}

It calculates average of all last three non zero entries. What I want it to calculate the average of say: Q44:Q46 and not take into account entry at Q47, which is the last entry. Currenly it is calculating average from Q45 to Q47.

Help is soliciated and apprecialted. Thanks in advance.

RGDS,

Rizvi

 

[Phuoc]

Try this:

=AVERAGE(IF(ROW(Q5:Q55)=LARGE(IF(Q5:Q55,ROW(Q5:Q55)),{2,3,4}),IF(Q5:Q55,Q5:Q55)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kvsrinivasamurthy]

Try

=SUM(IF(ROW($Q$41:$Q$47)=TRANSPOSE(LARGE(IF($Q$41:$Q$47<>"",ROW($Q$41:$Q$47),""),ROW($2:$4))),$Q$41:$Q$47,""))/3

This is an ARRAY formula.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[RIZVI]

Dear Phuoc thanks a lot. This worked. Highly obliged.

RGDS

Rizvi.M.H.

 

[Peter_SSs]

Dear Phuoc thanks a lot. This worked. Highly obliged.

That's good that it worked for you, but it would still help for the future (or possibly allow a simpler formula for this problem) if you addressed post #3.

 

[RIZVI]

That's good that it worked for you, but it would still help for the future (or possibly allow a simpler formula for this problem) if you addressed post #3.

I have done the needful and ticked at excel 2010

 

[Fluff]

Thanks for that