Average issue

C

CherryMan

New Member
Joined
Jul 6, 2008
Messages
14
Please look in the "Average" column. Is it possible to be able to include and NOT include the 0 as part of the average? I have included all of my formulas to help if necessary. Thank you very much
72<o:p></o:p>
80/88<o:p></o:p>
100/113<o:p></o:p>
125/138<o:p></o:p>
UNDER 13<o:p></o:p>
=COUNTIF(O5:O124,"<13")<o:p></o:p>
=COUNTIF(Q5:Q124,"<13")<o:p></o:p>
=COUNTIF(S5:S124,"<13")<o:p></o:p>
=COUNTIF(U5:U124,"<13")<o:p></o:p>
UNDER 14<o:p></o:p>
=COUNTIF(O5:O124,"<14")<o:p></o:p>
=COUNTIF(Q5:Q124,"<14")<o:p></o:p>
=COUNTIF(S5:S124,"<14")<o:p></o:p>
=COUNTIF(U5:U124,"<14")<o:p></o:p>
UNDER 15<o:p></o:p>
=COUNTIF(O5:O124,"<15")<o:p></o:p>
=COUNTIF(Q5:Q124,"<15")<o:p></o:p>
=COUNTIF(S5:S124,"<15")<o:p></o:p>
=COUNTIF(U5:U124,"<15")<o:p></o:p>
TOTAL ENTRIES<o:p></o:p>
=COUNTIF(O5:O124,">0")<o:p></o:p>
=COUNTIF(Q5:Q124,">0")<o:p></o:p>
=COUNTIF(S5:S124,">0")<o:p></o:p>
=COUNTIF(U5:U124,">0")<o:p></o:p>
% UNDER 13<o:p></o:p>
=IFERROR(B35/B38,0)<o:p></o:p>
=IFERROR(C35/C38,0)<o:p></o:p>
=IFERROR(D35/D38,0)<o:p></o:p>
=IFERROR(E35/E38,0)<o:p></o:p>
% UNDER 14<o:p></o:p>
=IFERROR(B36/B38,0)<o:p></o:p>
=IFERROR(C36/C38,0)<o:p></o:p>
=IFERROR(D36/D38,0)<o:p></o:p>
=IFERROR(E36/E38,0)<o:p></o:p>
% UNDER 15<o:p></o:p>
=IFERROR(B37/B38,0)<o:p></o:p>
=IFERROR(C37/C38,0)<o:p></o:p>
=IFERROR(D37/D38,0)<o:p></o:p>
=IFERROR(E37/E38,0)<o:p></o:p>

<tbody>
</tbody>
Pressures<o:p></o:p>
 72<o:p></o:p>
 80/88<o:p></o:p>
 100/113<o:p></o:p>
 125/138 <o:p></o:p>
 Average <o:p></o:p>
Max<o:p></o:p>
 =MAX(O5:O124)<o:p></o:p>
 =MAX(Q5:Q124)<o:p></o:p>
 =MAX(S5:S124)<o:p></o:p>
 =MAX(U5:U124)<o:p></o:p>
 =IFERROR(AVERAGEIF(B4:E4,">0",B4:E4),0)<o:p></o:p>
Min<o:p></o:p>
 =MIN(O5:O124)<o:p></o:p>
 =MIN(Q5:Q124)<o:p></o:p>
 =MIN(S5:S124)<o:p></o:p>
 =MIN(U5:U124)<o:p></o:p>
 =IFERROR(AVERAGEIF(B5:E5,">0",B5:E5),0)<o:p></o:p>
Average<o:p></o:p>
 =IFERROR(AVERAGEIF(O5:O124,">0",O5:O124),0)<o:p></o:p>
 =IFERROR(AVERAGEIF(Q5:Q124,">0",Q5:Q124),0)<o:p></o:p>
 =IFERROR(AVERAGEIF(S5:S124,">0",S5:S124),0)<o:p></o:p>
 =IFERROR(AVERAGEIF(U5:U124,">0",U5:U124),0)<o:p></o:p>
 =IFERROR(AVERAGEIF(B6:E6,">0",B6:E6),0)<o:p></o:p>
Below psi<o:p></o:p>
 Average<o:p></o:p>
Under 13<o:p></o:p>
 =B39<o:p></o:p>
 =C39<o:p></o:p>
 =D39<o:p></o:p>
 =E39<o:p></o:p>
 =IFERROR(AVERAGEIF(B8:E8,">0",B8:E8),0)<o:p></o:p>
Under 14<o:p></o:p>
 =B40<o:p></o:p>
 =C40<o:p></o:p>
 =D40<o:p></o:p>
 =E40<o:p></o:p>
 =IFERROR(AVERAGEIF(B9:E9,">0",B9:E9),0)<o:p></o:p>
Under 15<o:p></o:p>
 =B41<o:p></o:p>
 =C41<o:p></o:p>
 =D41<o:p></o:p>
 =E41<o:p></o:p>
 =IFERROR(AVERAGEIF(B10:E10,">0",B10:E10),0)<o:p></o:p>

<tbody>
</tbody>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
I need it calculated in certain conditions only. If you look at the below sheet from the under 13 average is higher than the under 14. Reason being is that it did not calculate the zero. In this instance I would need the 0 to be included in the average. The majority of the time, this would not be an issue, but it happened. Sorry for the terrible wording and I hope this helps.
Pressures7280/88100/113125/138Average
Max13.118.915.424.117.9
Min8.013.27.19.49.4
Average11.115.411.617.313.8
Below PSIAverage
Under 1395%0%78%5%59%
Under 14100%23%93%13%57%
Under 15100%45%95%25%66%

<colgroup><col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <tbody>
</tbody>
 
Upvote 0
it is not a correct average if the under 13 is higher than under 14. The correct average including the 0 would have been 44%. But because I told Excel not to include the 0 that rose my average above. So I guess that if the under 14 and 15 have a number greater than 0 then the 0 must be calculated. I hope I am helping you get closer, but do not be afraid to ask more questions.
 
Upvote 0
CherryMan said:
it is not a correct average if the under 13 is higher than under 14. The correct average including the 0 would have been 44%. But because I told Excel not to include the 0 that rose my average above. So I guess that if the under 14 and 15 have a number greater than 0 then the 0 must be calculated. I hope I am helping you get closer, but do not be afraid to ask more questions.
Click to expand...

Why don't you post some data along with what you want to see as outcome?
 
Upvote 0
Here is the data as it stand now. The under 13 should have included the 0 in its calculation for this instance. The average should be 44%. My formulas are in my original post.
Below PSIAverage
Under 1395%0%78%5%59%
Under 14100%23%93%13%57%
Under 15100%45%95%25%66%

<colgroup><col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <tbody>
</tbody>
 
Upvote 0
CherryMan said:
Here is the data as it stand now. The under 13 should have included the 0 in its calculation for this instance. The average should be 44%. My formulas are in my original post.
Below PSI
Average
Under 13
95%
0%
78%
5%
59%
Under 14
100%
23%
93%
13%
57%
Under 15
100%
45%
95%
25%
66%

<TBODY>
</TBODY>
Click to expand...

Below PSIAverageAverage > 0
Under 1395%0%78%5%45%0.593333333
Under 14100%23%93%13%57%0.5725
Under 15100%45%95%25%66%0.6625

<COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4152" width=117><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4892" width=138><TBODY>
</TBODY>

F2, copied down:

=AVERAGE(B2:E2)

G2, copied down:

=AVERAGEIF(B2:E2,">0",B2:E2)
 
Upvote 0
I thought about doing the same thing, but wondering if there is a way that I would not have to do that. Thanks Aladin.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Formula to Find the Average of Negative Time
Replies
6
Views
439
pjmorris
P
Romano_odK
  • Question
How many months am I covering in %?
Replies
0
Views
266
Romano_odK
Romano_odK
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
815
arthurbr
arthurbr
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
388
MrDB4Excel
MrDB4Excel
gtd526
  • Solved
Average specific value if listed on any worksheet
Replies
7
Views
379
Tetra201
Tetra201

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

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