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
|