How to average the last 10 numbers in a row?

[John P]

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.

 

[Bernie Deitrick]

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

 

[Mike H]

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

 

[Mike H]

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

 

[John P]

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]

John,

Last ten positive 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 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

 

[Lars-Åke Aspelin]

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.

Try this:

=SUMPRODUCT(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4>0)-1,10)):BX4,--(INDEX(B4:BX4,LARGE(COLUMN(B4:BX4)*(B4:BX4>0)-1,10)):BX4>0))/10

for the 10 last numbers greater than 0

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

for the 10 last numbers less than 0

It does not matter if there are any 0 present or not.
If you consider 0 as a positive number, then change > to >= in three
places in the first formula

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Replace X4 with BX4 and these formulae will work better.

Lars-Åke

 

[John P]

Thank you for your solution. It takes me some time to understand the logic
but at least your formulas are the familiar type found in Excel textbooks.
Another kind brother gave me a correct formula but he used a type of nested
IFs which I have not seen before and struggled to understand:
IF(IF(.....),IF(...))...) with the N function.

Thank you.

 

[John P]

Thanks Mike for your help. Appreciate it.

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