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

=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

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

#VALUE!




"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

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

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

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

=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))

Matt wrote:
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