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
Last edited by g_teacher_1848; 04-29-2021 at 06:47 AM.
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),"")
Last edited by etaf; 04-29-2021 at 06:55 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
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
Thank you, Pete. I have just updated the post and added the file as an attachment.
George
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
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
Last edited by g_teacher_1848; 04-29-2021 at 07:39 AM.
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,Originally Posted by Pete_UK
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
No sample data in your w/book!
Pete's formula works.
File attached, with the formula in AL8 and copied down, and some made-up numbers.
Hope this helps.
Pete
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)))
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)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks