Text to equal "0"


G

Guest

I have a column and in Row 1 I need the average, Row 2 the Minimum & Row 3
the Max.

I allow the user to input from rows 6 - 37 and my formulas are as follows:

Row1: =AVERAGE(A6:A37)
Row2: =MIN(A6:A37)
Row3: =MAX(A6:A37)

this works as it should however I have a value of "bdl" that a user may
input and I need that to equal zero for the formulas to work correctly
I cannot just enter zero as "bdl" is an acronym for below defined limits and
gov regulations require that it read as such. I thought I could use an IF
statement in the formula but I cannot get it to work.

I appreciate all help in this equation and thank you for your time.

MM
 
Ad

Advertisements

N

N Harkawat

=AVERAGE(--SUBSTITUTE(a6:a37,"bdl",0))
array entered (ctrl+shift+enter)

same way for the other functions
 
G

Guest

Looked good to me but ,
I pasted that in my field for the averages and I get :

#VALUE!
 
G

Guest

Just a heads up, if the OP has any blank cells your formula will return an
error,
it can also be solved as

=AVERAGE(IF(A6:A37="bdl",0,IF(A6:A37<>"",A6:A37)))

array entered

Of course if there never are any blanks your formula will work fine




regards,

Peo Sjoblom
 
G

Guest

ah-ha

Thanks so much.



Peo Sjoblom said:
Just a heads up, if the OP has any blank cells your formula will return an
error,
it can also be solved as

=AVERAGE(IF(A6:A37="bdl",0,IF(A6:A37<>"",A6:A37)))

array entered

Of course if there never are any blanks your formula will work fine




regards,

Peo Sjoblom
 
C

CLR

The first formula also crashes if there is any other TEXT in the range
besides the "bdl"...............whereas Peo's formula works fine.

Vaya con Dios,
Chuck, CABGx3
 
Ad

Advertisements

A

Aladin Akyurek

=SUM(A6:A37)/(COUNT(A6:A37)+COUNTIF(A6:A37,"bdl"))

=CHOOSE(IF(COUNTIF(A6:A37,"bdl"),1,2),MIN(A6:A37,0),MIN(A6:A37))

=CHOOSE(IF(COUNTIF(A6:A37,"bdl"),1,2),MAX(A6:A37,0),MAX(A6:A37))
 

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