How to average the last 10 numbers in a row?
Hi,
May be this
=AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <""),10)):BX4)
or this
=AVERAGE(INDEX(A4:BX4,LARGE(COLUMN(A4:BX4)*(A4:BX4 <0),10)):BX4)
This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array
Mike
Both ara array formula
"John P" wrote:
How can I write a formula to calculate the average for the last 10
positive/negative numbers in a row eg. B4:BX4 if:
1. Row B4:BX4 has both positive and negative numbers without zeros
2. Row B4:BX4 has both positive and negative numbers and may contain zeros
I have tried my best for the last 2 weeks and hope somebody can enlighten me
here. Thanks.
|