Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text to equal "0"
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
How can I get text to equal a numerical value in Excel? | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |