John,

Last ten positive numbers, without zeroes:
=AVERAGE(IF(B4:BX40,
IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX40,COLUMN(B4:X4) ,0),10), B4:BX4)))
Last ten numbers greater than or equal to zero:
=AVERAGE(IF(B4:BX4=0,
IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4=0,COLUMN(B4:X4 ),0),10), B4:BX4)))
Last ten negative numbers, without zeroes:
=AVERAGE(IF(B4:BX4<0,
IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<0,COLUMN(B4:X4) ,0),10), B4:BX4)))
Last ten negative greater than or equal to zero:
=AVERAGE(IF(B4:BX4<=0,
IF(COLUMN(B4:BX4)=LARGE(IF(B4:BX4<=0,COLUMN(B4:X4 ),0),10), B4:BX4)))

Again, array entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP



"John P" wrote in message
...
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.