Average If (Value between 1 and 100)

D

driller

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
 
D

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
 
D

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?
 
D

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


Don said:
Except if there is a 0 or a said:
[quoted text clipped - 13 lines]
 
D

driller

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

DAI via OfficeKB.com said:
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


Don said:
Except if there is a 0 or a said:
[quoted text clipped - 13 lines]
happy holiday
mine driller
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top