Average last 10 entries (ignoring zeros)

Use this formula =AVERAGEIF(A2:A25,">0")
Change the Row/Column accordingly

That's great thanks, but I'm also trying to average just the last 10 entriers.

New version attached.

Try this array formula

=AVERAGEIF(DA10:INDEX(10:10,LARGE(IF(B10:DA10>0,COLUMN(B10:DA10)),10)),">0")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Originally Posted by AliGW

The attached sheet is no use as it draws data from another sheet that we don't have.

Ali, in other threads where the data consistency is less predictable, I would agree with you, however the sample sheet gives enough information to work from in this instance, thinking logically we know that the formulas in row 10 must return integers, so substituting some appropriate values is adequate to test a formula.

I used =RANDBETWEEN(0,3) in B10:DA10 to produce suitable data for testing.

Jason - thanks for this. It works, but this shows the first 10 inputs, rather than the last 10 (which i need). The data is inputted from right to left.

Thanks again for your help on this one.

Ali, my apologies if that came across as harsh criticism, that wasn't how it was intended. Thinking that you have an interest in learning and understanding, I was trying (badly) to point out that it is sometimes, but not always possible to work without detailed and accurate samples.

Rather than cluttering the thread too much with a side discussion, I'll send you a p.m. later to guide you through what I was thinking, never know when you might be able to use it

Boris, I looked at what was needed but didn't read the dates in the rows above, just assumed (incorrectly) that the last entries would be on the left.

In between slices of rather chewy humble pie, I was able to reverse the formula.

=AVERAGEIF(B10:INDEX(10:10,SMALL(IF(B10:DA10>0,COLUMN(B10:DA10)),10)),">0")

Still needs to be array confirmed.

Jason sir, you are a wizard!

Thank you!

Looking at Trevor's formula, I'm wondering if I had the wrong idea of last 10 ignoring zeros.

The formula that I suggested averages the last 10 non-zero values, so with a single zero in the last 10, the formula would look at the last 11 values and exclude the zero, rather than average the 9 non zero values in the last 10 (which Trevor's formula appears to do).

Could you confirm which of us is on the right track.

I'm going to throw in a revised array formula based on latest observations.

=AVERAGEIF(B10:INDEX(B10:DA10,IFERROR(MATCH(2,1/ISBLANK(B10:DA10)),0)+10),">0")

@Trevor: I'll leave you to revise your formula if it's needed, I can see what you've done with it, but I don't like cannibalizing other peoples methods.

That same thought did cross my mind, but I thought I would leave that pending confirmation from the OP.

One of the consequences of entering data backwards.

I guess you could just replace the 3 references to B2: with INDEX(10:10,2): but it does start looking a bit messy when you start doing things like that.