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.