How to average the last 10 numbers in a row?
Thanks Bernie. Sorry I didn't make myself clear. What I meant was how to
calculate the average for the last 10 positive numbers or last 10 negative
numbers in a row that has both positive and negative numbers in case 1
without zero and case 2 with zeros.
Your formula gives the average for the last 10 numbers.
"Bernie Deitrick" wrote:
John,
Array enter (enter using Ctrl-Shift-Enter instead of just Enter)
To include 0s but ignore blanks
=AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<"",COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4,
LARGE(IF(B4:BX4<"",COLUMN(B4:BX4 ),0),1))))
To ignore zeroes and blanks:
=AVERAGE(INDIRECT(ADDRESS(4, LARGE(IF(B4:BX4<0,COLUMN(B4:X4 ),0),10))& ":" & ADDRESS(4,
LARGE(IF(B4:BX4<0,COLUMN(B4:BX4 ),0),1))))
I'm sure there are other ways to do this without INDIRECT but this was easy and it works.... :-)
HTH,
Bernie
MS Excel MVP
"John P" wrote in message
...
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.
|