Average non-continuous cells if not '0'

1. Average non-continuous cells if not '0'

Hello all,

I would like to average 10 cells, but only if they do not contain 0.

For example, if only 9 cells are filled it will divide by 9, not 10.

Capture.PNG

You can see highlighted below the cells that I would like to average if not 0 or blank.

If somebody could give a formula, that would be amazing!

Thank you

2. Re: Average non-continuous cells if not '0'

averageif( K8:AK8, "<>0")
IGNORE - just seen more full info again
as requested - spreadsheet would help - see yellow banner

maybe
=IFERROR(SUM(M8,P8,S8,V8,Y8,AB8,AE8,AH8,AK8)/INDEX(FREQUENCY((M8,P8,S8,V8,Y8,AB8,AE8,AH8,AK8),0),2),"")

3. Re: Average non-continuous cells if not '0'

A picture is not much use to us - better to attach a sample Excel workbook, as detailed in the yellow banner at the top of the screen.

Pete

4. Re: Average non-continuous cells if not '0'

Thank you, Pete. I have just updated the post and added the file as an attachment.

George

5. Re: Average non-continuous cells if not '0'

It would also help if there was some made-up data in there.

What are we averaging? From your initial picture it looks like the Gra columns - will these contain numbers?

Pete

6. Re: Average non-continuous cells if not '0'

Thank you all for offering support.

I would like to average; J8, M8, P8, S8, V8, Y8, AB8, AE8, AH8 and AK8. I would like the average of these to be inputted into AL8. However, it is important that if any of these contain '0', they are not included in the average.

It is for students at a school, so if they miss a test, that test does not count towards their average.

I will be adding the same formula to each row to calculate all their averages.

I hope that this is clear enough?

For example, with this test data in the first row giving an average of 7 (as excluding 0's):
Capture1.PNG

7. Re: Average non-continuous cells if not '0'

You can use this formula in cell AL8:

=IFERROR(AVERAGEIFS(H8:AK8,\$H\$7:\$AK\$7,"Gra",H8:AK8,">0"),"")

then copy down as required. You can change the "Gra" to one of the other headings on row 7 if you want to average other columns. The IFERROR caters for them all being empty (or zero).

Hope this helps.

Pete

8. Re: Average non-continuous cells if not '0'

Originally Posted by Pete_UK
You can use this formula in cell AL8:

=IFERROR(AVERAGEIFS(H8:AK8,\$H\$7:\$AK\$7,"Gra",H8:AK8,">0"),"")

then copy down as required. You can change the "Gra" to one of the other headings on row 7 if you want to average other columns. The IFERROR caters for them all being empty (or zero).

Hope this helps.

Pete
Hi Pete,

That doesn't seem to work. It is only cells J8, M8, P8, S8, V8, Y8, AB8, AE8, AH8 and AK8 that I would like to average, if not 0.

Thank you, still.

George

9. Re: Average non-continuous cells if not '0'

No sample data in your w/book!

10. Re: Average non-continuous cells if not '0'

Pete's formula works.

11. Re: Average non-continuous cells if not '0'

File attached, with the formula in AL8 and copied down, and some made-up numbers.

Hope this helps.

Pete

12. Re: Average non-continuous cells if not '0'

In case you don't have AVERAGEIFS function, since I think AVERAGEIFS is available since 2019, but you seem to have 2016.

=AVERAGE(--IF(\$H\$7:\$AK\$7="Gra",IF(H8:AK8>0,H8:AK8)))

13. Re: Average non-continuous cells if not '0'

AVERAGEIFS was introduced with XL2007 (along with SUMIFS, COUNTIFS), so there should be no problem using it.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)