Find average of last 4 non zero numbers

[Infinity99]

Hi I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

Any help is appreciated... thanks kindly. Let me know if there is anything else I need to clarify.

[Richard Buttrey]

Hi, and welcome to the forum.

Does the 5th Total value cell in each block contain the characters "Total = " as well as the total number, or just the total number itself?

Rgds

[Infinity99]

All the total values are just values. There is no text in the row, so it'd be 7 , 7 , 7, 7 , 28 , 8 , 8 etc.

[Ron Coderre]

With
A1:AN1 containing your posted values....

This formula returns the average of the last 4 totals that are greater than zero

Please Login or Register  to view this content.

In your example, the formula returns: 31
(the average of 32,28,32,32)

Is that something you can work with?

[Infinity99]

Hi Richard/Ron, thanks for your response

Does this suggested formula take the 4 largest numbers in the array or does it take the last 4 (non-zero) values in the array? (i'm not familiar with the LARGE function yet)

I have since reorganized my data so it may be simpler to create the formula.

It now looks like this (just totals):

28 , 32 , 0 , 32 , 28 , 32 , 0 , 32

[Ron Coderre]

To answer your first question....The formula I posted averages the LAST 4 totals that are greater than zero.

Regarding the new scenario of only consecutive totals...
With
A1:H1 containing these values
28 , 32 , 0 , 32 , 28 , 32 , 0 , 32

This formula returns the average of the last 4 totals that are greater than zero

Please Login or Register  to view this content.

In the above example, the formula returns: 31
(the average of 32,28,32,32)

Does that help?

[Infinity99]

Ron, you sir are a gentleman and a scholar.

I hope I can help someone on this board as much as you just did. If you're in the Toronto area, your next drink is on me.

[Ron Coderre]

...thanks for the kind words (and the offer).