Average If (Value between 1 and 100)

D

DAI

Hello,

I need to show the average of a column (cells M8 to m5000)

As this column is populated by a formula =(L8/H8)*100

And in turn this is populated by the data entered into L8 and H8.

------------------------------------------------------------------------------
----------------------

My problem is - the #DIV/0! error (caused by the first set of data in the
chain not being entered yet)

Therefore the simple AVERAGE function can not be applied.

I was playing with the idea of

=average(if(M8:M5000)<100)

but this is not a valid formula.

Any Help would be appreciated.
 
D

Don Guillett

This is an array formula which must be entered using ctrl+shift+enter

=AVERAGE(IF(ISNUMBER(E2:E200),E2:E200,""))
 
T

Teethless mama

=AVERAGE(IF((M8:M5000>1)*(M8:M5000<100),M8:M5000))

ctrl+shift+enter (not just enter)
 
D

driller

1. better revised the formula on M8 because
there are two type of unwanted result that will appear due to Incomplete
Data, either 0 or #DIV/0!...the 0 result will be counted in your averaging
formula.

=IF(AND(H8="",L8=""),"No Data",IF(OR(H8="",L8=""),"Data
Incomplete",(L8/H8)*100))

2. averaging formula of a Complete data only.
=average(if(M8:M5000<100,M8:M5000)
press Ctrl+shift+enter on edit mode, excel will insert the braces { } for a
verified array formula.
take care since the {=average(if( ))} will count even the blank cell for
its average.

or you can clasically use / type in
=sum(M8:M5000)/count(M8:M5000)

these averaging formula will work if you use the suggested If formula on 1.

"try to correct typos for better understanding and learning"
good luck.
 
D

DAI

Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formatting to hide the errors.

2 I used the ctrl+Shift+Enter and have the{} around the formula but still get
the #DIV/0! Error

Also the or =sum(M8:M5000)/count(M8:M5000) formula would have worked well
(just me over complicating things)


Thanks

Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formating
1. better revised the formula on M8 because
there are two type of unwanted result that will appear due to Incomplete
Data, either 0 or #DIV/0!...the 0 result will be counted in your averaging
formula.

=IF(AND(H8="",L8=""),"No Data",IF(OR(H8="",L8=""),"Data
Incomplete",(L8/H8)*100))

2. averaging formula of a Complete data only.
=average(if(M8:M5000<100,M8:M5000)
press Ctrl+shift+enter on edit mode, excel will insert the braces { } for a
verified array formula.
take care since the {=average(if( ))} will count even the blank cell for
its average.

or you can clasically use / type in
=sum(M8:M5000)/count(M8:M5000)

these averaging formula will work if you use the suggested If formula on 1.

"try to correct typos for better understanding and learning"
good luck.
[quoted text clipped - 19 lines]
Any Help would be appreciated.
 
D

driller

Dai,
1. did you test the 0 result on column M, cause this will be included in
your averaging even there is no data (blank) on H8 Yet. and it cannot be
hidden inside an averaging formula.
2. your post requires a conditional formula (value between 1 and 100)

i hope your post can clarify the real formula needed, so other viewers can
learn also.
happy thanksgiving..

DAI said:
Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formatting to hide the errors.

2 I used the ctrl+Shift+Enter and have the{} around the formula but still get
the #DIV/0! Error

Also the or =sum(M8:M5000)/count(M8:M5000) formula would have worked well
(just me over complicating things)


Thanks

Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formating
1. better revised the formula on M8 because
there are two type of unwanted result that will appear due to Incomplete
Data, either 0 or #DIV/0!...the 0 result will be counted in your averaging
formula.

=IF(AND(H8="",L8=""),"No Data",IF(OR(H8="",L8=""),"Data
Incomplete",(L8/H8)*100))

2. averaging formula of a Complete data only.
=average(if(M8:M5000<100,M8:M5000)
press Ctrl+shift+enter on edit mode, excel will insert the braces { } for a
verified array formula.
take care since the {=average(if( ))} will count even the blank cell for
its average.

or you can clasically use / type in
=sum(M8:M5000)/count(M8:M5000)

these averaging formula will work if you use the suggested If formula on 1.

"try to correct typos for better understanding and learning"
good luck.
[quoted text clipped - 19 lines]
Any Help would be appreciated.
 
T

Teethless mama

=IF(ISERROR(M8:M5000),"",AVERAGE(IF((M8:M5000>1)*(M8:M5000<100),M8:M5000)))

ctrl+shift+enter (not just enter)
 
D

driller

Average If (Vaalue between 1 and 100)

maybe to modify minor change of Mama's formula ">" into ">="

=IF(ISERROR(M8:M5000),"",AVERAGE(IF((M8:M5000>=1)*(M8:M5000=<100),M8:M5000)))
thanks mama...
 
D

DAI

Sorry all,

Im getting a bit confused here,

The solution posted by Don is the only one that works.

=AVERAGE(IF(ISNUMBER(M8:M5000),M8:M5000,""))

with the Ctrl+Shift+Enter.

Thank you all.
 
D

Don Guillett

The question was not how to average between 1-100. It was how to average
when div/o. Of course, fixing the original formula to NOT get the error is
best or only put in the formulas as needed by an automatic macro or use my
solution.
 
D

driller

oopss mama,
=IF(ISERROR(M8:M5000),"", ---result is blank cause of #DIV/0! ??
we're still confused.
 
D

driller

sorry Dai for being confused
but please take a look at your Question thread "Value between 1 and 100"
which is a specific conditional question..
You will have 0 result on M8, when L8 is blank and H8 has no data yet ?
Average will fail, even for the viewers.

try to check it so viewers will not be confused.
 
D

driller

i inherited now a typo habit !

You will have 0 result on M8, when L8 has Value and H8 has no data yet ?
*Average will fail, even for the viewers.*
 
D

driller

Don, the DAI is asking Average(If solution or thru a formula, under the
Worksheet function forum.
regards
 
D

driller

if i have a sales agent, maybe like u, i dont know what will happen because u
cannot convince a prospective buyer.

software is convinced technically not thru emotions.

more sales for you, maybe, i dont know, i'm confused of your technical reply.
 

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