Average If (Value between 1 and 100)

[Guest]

excuse me Dai,

"Average If (Value between 1 and 100)"

for the sake of the worksheet function forum readers,
here's a classic non-acrobatic response to a clearly titled question.

=(SUMIF(m8:m5000,"<=100",m8:m5000)-SUMIF(m8:m5000,"<1",m8:m5000))/(COUNTIF(m8:m5000,"<=100")-COUNTIF(m8:m5000,"<1"))

happy holiday
mine driller

 

[DAI via OfficeKB.com]

Driller,

Yes this also works,

Thank you.

excuse me Dai,

"Average If (Value between 1 and 100)"

for the sake of the worksheet function forum readers,
here's a classic non-acrobatic response to a clearly titled question.

=(SUMIF(m8:m5000,"<=100",m8:m5000)-SUMIF(m8:m5000,"<1",m8:m5000))/(COUNTIF(m8:m5000,"<=100")-COUNTIF(m8:m5000,"<1"))

happy holiday
mine driller

 

[Don Guillett]

Except if there is a 0 or a <0 or a >100 in the list. Is that what you want?
Or, do you just want to remove the div/0 cells?

 

[DAI via OfficeKB.com]

Don

Your right but as the cell holds a percentage value it will never go over 100.


Also as you suggested above I have corrected the first equation in the chain
with

=IF(OR(L30="",H30=""),"",(L30/H30)*100) - which now doen't give an error.

So I can probably adjust the last equation to suit now.

Thnaks anyway

Driller,

[quoted text clipped - 13 lines]

 

[Guest]

good work DAI, avoid error results by replacing it with Blanks"". Your
workbook will be neat and easy to audit, and the reason of summing or
averaging will be on the real world of logic since there are no errors
in-between your source references.
more power...happy holidays

Don

Your right but as the cell holds a percentage value it will never go over 100.


Also as you suggested above I have corrected the first equation in the chain
with

=IF(OR(L30="",H30=""),"",(L30/H30)*100) - which now doen't give an error.

So I can probably adjust the last equation to suit now.

Thnaks anyway

Driller,

[quoted text clipped - 13 lines]

happy holiday
mine driller