Average issue

[CherryMan]

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></o>	80/88<o></o>	100/113<o></o>	125/138<o></o>
UNDER 13<o></o>	=COUNTIF(O5:O124,"<13")<o></o>	=COUNTIF(Q5:Q124,"<13")<o></o>	=COUNTIF(S5:S124,"<13")<o></o>	=COUNTIF(U5:U124,"<13")<o></o>
UNDER 14<o></o>	=COUNTIF(O5:O124,"<14")<o></o>	=COUNTIF(Q5:Q124,"<14")<o></o>	=COUNTIF(S5:S124,"<14")<o></o>	=COUNTIF(U5:U124,"<14")<o></o>
UNDER 15<o></o>	=COUNTIF(O5:O124,"<15")<o></o>	=COUNTIF(Q5:Q124,"<15")<o></o>	=COUNTIF(S5:S124,"<15")<o></o>	=COUNTIF(U5:U124,"<15")<o></o>
TOTAL ENTRIES<o></o>	=COUNTIF(O5:O124,">0")<o></o>	=COUNTIF(Q5:Q124,">0")<o></o>	=COUNTIF(S5:S124,">0")<o></o>	=COUNTIF(U5:U124,">0")<o></o>
% UNDER 13<o></o>	=IFERROR(B35/B38,0)<o></o>	=IFERROR(C35/C38,0)<o></o>	=IFERROR(D35/D38,0)<o></o>	=IFERROR(E35/E38,0)<o></o>
% UNDER 14<o></o>	=IFERROR(B36/B38,0)<o></o>	=IFERROR(C36/C38,0)<o></o>	=IFERROR(D36/D38,0)<o></o>	=IFERROR(E36/E38,0)<o></o>
% UNDER 15<o></o>	=IFERROR(B37/B38,0)<o></o>	=IFERROR(C37/C38,0)<o></o>	=IFERROR(D37/D38,0)<o></o>	=IFERROR(E37/E38,0)<o></o>

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[Aladin Akyurek]

I thought about doing the same thing, but wondering if there is a way that I would not have to do that. Thanks Aladin.

Sorry. Not have to do what?

 

[CherryMan]

Below PSI	Average	Average > 0
Under 13	95%	0%	78%	5%	45%	0.593333333
Under 14	100%	23%	93%	13%	57%	0.5725
Under 15	100%	45%	95%	25%	66%	0.6625

<tbody>
</tbody>

F2, copied down:

=AVERAGE(B2:E2)

G2, copied down:

=AVERAGEIF(B2:E2,">0",B2:E2)

I did not want to create 2 columns. I was hoping that some conditions could be created that would allow the average to have/not have 0 included.

 

[Aladin Akyurek]

I did not want to create 2 columns. I was hoping that some conditions could be created that would allow the average to have/not have 0 included.

Below PSI					>0
Under 13	95%	0%	78%	5%	59%
Under 14	100%	23%	93%	13%	57%
Under 15	100%	45%	95%	25%	66%

<TBODY>
</TBODY>

F1 houses either >0 or is empty.

F2, copied down:

=AVERAGEIF(B2:E2,IF($F$1="","<>",$F$1))

If you empty F1, the average percentages will change accordingly.

 

[CherryMan]

Below PSI					>0
Under 13	95%	0%	78%	5%	59%
Under 14	100%	23%	93%	13%	57%
Under 15	100%	45%	95%	25%	66%

<tbody>
</tbody>

F1 houses either >0 or is empty.

F2, copied down:

=AVERAGEIF(B2:E2,IF($F$1="","<>",$F$1))

If you empty F1, the average percentages will change accordingly.

This is the exact reason of my question. In this case the 0 should be calculated in the average because there was nothing that was under 14. That 0 should be calculated in the under 13 average. So if there is a number above 0 in any of the cells B2:E4, then the 0 must be calculated. Conversely, if there is no number greater than 0 in B2:B4, C2:C4, D2:D4, E2:E4, then the 0 cannot be used in the average.

 

[Aladin Akyurek]

This is the exact reason of my question. In this case the 0 should be calculated in the average because there was nothing that was under 14. That 0 should be calculated in the under 13 average. So if there is a number above 0 in any of the cells B2:E4, then the 0 must be calculated. Conversely, if there is no number greater than 0 in B2:B4, C2:C4, D2:D4, E2:E4, then the 0 cannot be used in the average.

Not sure I follow. What do you want to see as result in the last column when we have:

Below PSI
Under 13	95%	0%	78%	5%
Under 14	100%	23%	93%	13%
Under 15	100%	45%	95%	25%

<COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4807" width=135><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3953" width=111><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4380" width=123><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2275" width=64><TBODY>
</TBODY>

Would you re-word the reasons for the expected results?

 

[CherryMan]

Not sure I follow. What do you want to see as result in the last column when we have:

Below PSI	Size 1	Size 2	Size 3	Size 4	Average
Under 13	95%	0%	78%	5%
Under 14	100%	23%	93%	13%
Under 15	100%	45%	95%	25%

<tbody>
</tbody>

Would you re-word the reasons for the expected results?

The average for the under 15, under 14 and under 13 should include the number from each of their cells. However, if say column, header E2 (Size 4), is empty then the average should NOT include the 0. So because the cells in C3 and C4 are greater than 0 the average for under 13 should include the 0 to be a correct average. If the cells C2:C4 are 0 because of no data for that size, then the zero cannot be included in the average.

 

[Aladin Akyurek]

The average for the under 15, under 14 and under 13 should include the number from each of their cells. However, if say column, header E2 (Size 4), is empty then the average should NOT include the 0. So because the cells in C3 and C4 are greater than 0 the average for under 13 should include the 0 to be a correct average. If the cells C2:C4 are 0 because of no data for that size, then the zero cannot be included in the average.

Still no cigare. Why did you leave the average column empty while I explicitly asked to post what you want to see there for each record?

 

[CherryMan]

Still no cigare. Why did you leave the average column empty while I explicitly asked to post what you want to see there for each record?

How can I show you when conditions have to be met. I know how to get the numbers without certain conditions, but it is creating the conditions that I am having a problem. Maybe my problem is impossible for Excel?

 

[Aladin Akyurek]

How can I show you when conditions have to be met. I know how to get the numbers without certain conditions, but it is creating the conditions that I am having a problem. Maybe my problem is impossible for Excel?

To start with: Just the conditions that must hold and the expected results.

 

[CherryMan]

To start with: Just the conditions that must hold and the expected results.

Below PSI	Size 1	Size 2	Size 3	Size 4	Average
Under 13	95%	0%	78%	5%
Under 14	100%	23%	93%	13%
Under 15	100%	45%	95%	25%

<tbody>
</tbody>

Lets try this: If size 1 cells B2:B4 are greater than 0 the below 13,14,15 must include the number in its calculation. If ALL the numbers B2:B4 are 0, then the below 13,14,15 CANNOT include the 0 in its calculation. So the average for the under 13 should include the number 0 in its calculation, because the numbers in C3 and C4 are greater than 0. If, say, all of the numbers C2:C4 are 0, then the average calculation CANNOT include the 0. Am I confusing you more yet?