Excluding Zero When Calculating Average

C

chris.glory.chun

New Member
Joined
Feb 19, 2010
Messages
16
Hi Mr Excel,

Would you please help me with the excel formula?

I would like to know how to caluculate average excluding Zeros.

For example,
2 students got 90 marks
1 student got 70
and 2 student got 0 mark!

In the case of above, what formula should i use to ignore 0 mark student when calculating the the average marks???

Your quick response or help will be a massive for excel dummies! :)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
chris.glory.chun said:
Hi Mr Excel,

Would you please help me with the excel formula?

I would like to know how to caluculate average excluding Zeros.

For example,
2 students got 90 marks
1 student got 70
and 2 student got 0 mark!

In the case of above, what formula should i use to ignore 0 mark student when calculating the the average marks???

Your quick response or help will be a massive for excel dummies! :)
Click to expand...
=SUM(A2:F2)/COUNTIF(A2:F2,">0")

will ignore 0 marks in A2:F2. Adjust the range to suit.
 
Upvote 0
Excel Workbook
ABCDE
1Exclude 0'sInclude 0's
2From AverageIn Average
3Joe9090For example,
4Bill90902 students got 90 marks
5Suzy70701 student got 70
6Henry00and 2 student got 0 mark!
7Barry00
883.3333333350
Sheet1
Excel 2007
Cell Formulas
RangeFormula
C8=AVERAGE(C3:C7)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
A few more options for you

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 199px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD>Excel 07</TD><TD>Excel 03</TD><TD>Excel 03 with descending mark</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Student 1</TD><TD style="TEXT-ALIGN: right">90</TD><TD style="TEXT-ALIGN: right">90</TD><TD style="TEXT-ALIGN: right">90</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Student 2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">90</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Student 3</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right">70</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Student 4</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Student 5</TD><TD style="TEXT-ALIGN: right">90</TD><TD style="TEXT-ALIGN: right">90</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">83.33333</TD><TD style="TEXT-ALIGN: right">83.33333</TD><TD style="TEXT-ALIGN: right">83.33333333</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B7</TD><TD>=AVERAGEIF(B2:B6,">0",B2:B6)</TD></TR><TR><TD>C7</TD><TD>{=AVERAGE(IF(C2:C6,C2:C6))}</TD></TR><TR><TD>D7</TD><TD>=SUM(D2:D6)/MATCH(1,D2:D6,-1)</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1
 
Upvote 0
chris.glory.chun said:
Hi Mr Excel,

Would you please help me with the excel formula?

I would like to know how to caluculate average excluding Zeros.

For example,
2 students got 90 marks
1 student got 70
and 2 student got 0 mark!

In the case of above, what formula should i use to ignore 0 mark student when calculating the the average marks???

Your quick response or help will be a massive for excel dummies! :)
Click to expand...


What if I have a huge list of positive and negative numbers. I want to find the average of non-zero numbers?

all the numbers in this range:

$C8:$HZ8,$C12:$HZ12,$C16:$HZ16,$C20:$HZ20,$C24:$HZ24,$C28:$HZ28,$C32:$HZ32,$C36:$HZ36,$C40:$HZ40,$C44:$HZ44,$C48:$HZ48,$C52:$HZ52,$C56:$HZ56,$C60:$HZ60,$C64:$HZ64,$C68:$HZ68,$C72:$HZ72,$C76:$HZ76,$C80:$HZ80,$C84:$HZ84,$C88:$HZ88,$C92:$HZ92,$C96:$HZ96,$C100:$HZ100,$C104:$HZ104,$C108:$HZ108,$C112:$HZ112,$C116:$HZ116,$C120:$HZ120,$C124:$HZ124,$C128:$HZ128,$C132:$HZ132,$C136:$HZ136,$C140:$HZ140,$C144:$HZ144,$C148:$HZ148,$C152:$HZ152,$C156:$HZ156,$C160:$HZ160,$C164:$HZ164,$C168:$HZ168,$C172:HZ172,$C176:$HZ176,$C180:$HZ180,$C184:$HZ184,$C188:$HZ188,$C192:$HZ192,$C196:$HZ196,$C200:$HZ200,$C204:$HZ204,$C208:$HZ208,$C212:$HZ212,$C216:$HZ216,$C220:$HZ220,$C224:$HZ224,$C228:$HZ228,$C232:$HZ232,$C236:$HZ236,$C240:$HZ240,$C244:$HZ244,$C248:$HZ248,$C252:$HZ252,$C256:$HZ256,$C260:$HZ260,$C264:$HZ264,$C268:$HZ268,$C272:$HZ272,$C276:$HZ276,$C280:$HZ280,$C284:$HZ284,$C288:$HZ288,$C292:$HZ292,$C296:$HZ296,$C300:$HZ300,$C304:$HZ304,$C308:$HZ308,$C312:$HZ312,$C316:$HZ316,$C320:$HZ320,$C324:$HZ324,$C328:$HZ328,$C332:$HZ332,$C336:$HZ336,$C340:$HZ340,$C344:$HZ344,$C348:$HZ348,$C352:$HZ352,$C356:$HZ356,$C360:$HZ360,$C364:$HZ364,$C368:$HZ368,$C372:$HZ372,$C376:$HZ376,$C380:$HZ380,$C384:$HZ384,$C388:$HZ388,$C392:$HZ392,$C396:$HZ396,$C400:$HZ400,$C404:$HZ404,$C408:$HZ408,$C412:$HZ412,$C416:$HZ416,$C420:$HZ420,$C424:$HZ424,$C428:$HZ428,$C432:$HZ432,$C436:$HZ436</SPAN>

I found that when i try the above formulas, it errors on the commas between partial rows sets.

Maybe there's an easier way to do this? (inherited spreadsheet)</SPAN>
 
Upvote 0
Hi stoopid,

Generally you will have more chance of a response if you start your own thread than if you revive an old thread, if it seems relevant you can always include a link to the old thread in yours.

I haven't been through the list of ranges in great detail, but it looks as if it's the same columns, every 4th row, this should work if that is correct, but if the pattern is broken then it will fail.

PHP: 
=AVERAGE(IF(MOD(ROW($C$8:$HZ$438),4)=0,IF($C$8:$HZ$438<>0,$C$8:$HZ$438)))

Which needs to be array confirmed using Ctrl Shift and Enter.
 
Upvote 0
Thank you Jason. I'm very new to forums and such, especially this one and figured this was the same topic as the above. I'll remember that for next time.
Very interesting and helpful. Thanks again
 
Upvote 0
Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW($C$8:$HZ$438)-ROW($C$8),4)=0,IF(ISNUMBER(1/$C$8:$HZ$438),$C$8:$HZ$438)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

apgmin
  • Question Question
Form for data entry
Replies
1
Views
235
rpaulson
rpaulson
S
  • Question Question
How to get average of percentages in a column with filtered cells and excluding zero
Replies
2
Views
1K
Fluff
Fluff
T
  • Question Question
Moving average last 5 numeric number, N/A for any zero cells and the omitting that for next moving average
Replies
3
Views
358
Cubist
Cubist
B
  • Question Question
Excluding a cell from MINIFS calculation - error when excluded cell is equal to first cell in range
Replies
2
Views
537
FormR
FormR
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
139
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top