Just realised how you can do it without cheating by including column A in the
range

=AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <"")-1,10)):BX4)

or

=AVERAGE(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4 <0)-1,10)):BX4)

Mike




"Mike H" wrote:

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.