Average If

[Rob P.]

I want to average the last 5 cells which are not null.
A B C D E F G H
Person A 39 47 47 43 52 50
Person B 59 56 59 51 52 48

Person A would be the average of D:H, Person B would be the average of C:H. What formula would I enter in Column I to accomplish this?

Thanks,
Rob P.

 

[T. Valko]

I want to average the last 5 cells which are not null.
A B C D E F G H
Person A 39 47 47 43 52 50
Person B 59 56 59 51 52 48

Person A would be the average of D:H, Person B would be the average of C:H. What formula would I enter in Column I to accomplish this?

Thanks,
Rob P.

Will there ALWAYS be at least 5 cells with numbers in them to average?

If not, then what should happen?

 

[Rob P.]

Yes there will be. Sorry for not using the HTML tool, I have yet to be able to get it to work at the office.

 

[T. Valko]

Yes there will be. Sorry for not using the HTML tool, I have yet to be able to get it to work at the office.

Try this array formula**:

=AVERAGE(H2:INDEX(B2:H2,LARGE(IF(B2:H2 < > "",COLUMN(B2:H2)-COLUMN(B2)+1),5)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust the ranges to suit.

 

[Rob P.]

Perfecto!!!!

You Rock!

 

[T. Valko]

Perfecto!!!!

You Rock!

You're welcome. Thanks for the feedback!