Average If (Value between 1 and 100)

[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.

 

[Don Guillett]

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

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

 

[DAI]

Thank you Don,

That worked great.

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

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

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[Teethless mama]

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

ctrl+shift+enter (not just enter)

 

[DAI]

Mama,

I tried your formula but i still get the #DIV/0! error.

Dons seams to be working well though.

Thanks anyway.

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

ctrl+shift+enter (not just enter)

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[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.

 

[driller]

excuse Dai,

what happen with your
=average(if(M8:M5000)<100)
your playing critera of less than 100.
i am confused....
thanks

Thank you Don,

That worked great.

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

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

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[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.

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[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..

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.

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[Teethless mama]

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

ctrl+shift+enter (not just enter)

 

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thank you Don,

That worked great.

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

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

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.

 

[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...

 

[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.

 

[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.

 

[driller]

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

 

[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.

 

[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.*

 

[driller]

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

 

[Don Guillett]

Once again, glad to help. All should ignore the postings by the troll.

 

[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.