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


"Matt" wrote in message
...
ah-ha

Thanks so much.



"Peo Sjoblom" wrote:

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

"N Harkawat" wrote:

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

same way for the other functions


"Matt" wrote in message
...
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