Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if Function problem
I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry |
#2
|
|||
|
|||
What's in F6?
You may want to use an empty cell and try: =isnumber(f6) And I'd stay away from putting a space character in the cell. It usually makes for trouble later on. I'd use: =IF(AND(SEARCH("*Total",C5),F6<""),F6*0.03,"") jerry wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry -- Dave Peterson |
#3
|
|||
|
|||
F6 can contain either a blank cell ,negative numbers or positive numbers
"Dave Peterson" wrote: What's in F6? You may want to use an empty cell and try: =isnumber(f6) And I'd stay away from putting a space character in the cell. It usually makes for trouble later on. I'd use: =IF(AND(SEARCH("*Total",C5),F6<""),F6*0.03,"") jerry wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry -- Dave Peterson |
#4
|
|||
|
|||
What's in it when you get the error?
Maybe... =IF(AND(SEARCH("*Total",C5),trim(F6)<""),F6*0.03, "") jerry wrote: F6 can contain either a blank cell ,negative numbers or positive numbers "Dave Peterson" wrote: What's in F6? You may want to use an empty cell and try: =isnumber(f6) And I'd stay away from putting a space character in the cell. It usually makes for trouble later on. I'd use: =IF(AND(SEARCH("*Total",C5),F6<""),F6*0.03,"") jerry wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
A positive numberical value is in F6 when i get the Value error
In the instance when the one cell contains total and the other cell contains something other than "", the appropriate calculation is executed. However, in the instance when the text"total" in not found and yet there is a numberical value(returns true) in the other cell, the value error is displayed. Jerry "Dave Peterson" wrote: What's in it when you get the error? Maybe... =IF(AND(SEARCH("*Total",C5),trim(F6)<""),F6*0.03, "") jerry wrote: F6 can contain either a blank cell ,negative numbers or positive numbers "Dave Peterson" wrote: What's in F6? You may want to use an empty cell and try: =isnumber(f6) And I'd stay away from putting a space character in the cell. It usually makes for trouble later on. I'd use: =IF(AND(SEARCH("*Total",C5),F6<""),F6*0.03,"") jerry wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
According to microsoft help on SEARCH function
If find_text is not found, the #VALUE! error value is returned. (this is okay,but i dont want the VALUE error to appear on my report. How do I get around this? thanks "jerry" wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry |
#7
|
|||
|
|||
On Fri, 30 Sep 2005 14:25:03 -0700, "jerry"
wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry SEARCH is probably returning the VALUE error by not finding Total in C5. Use this instead: ISNUMBER(SEARCH("*Total",C5)) Also, you probably want to use null strings rather than spaces in checking F6; and also in returning the value_if_false from this function; so: =IF(AND(ISNUMBER(SEARCH("*Total",C5)),F6<""),F6*0 .03,"") --ron |
#8
|
|||
|
|||
Ahhhhhhhhhhh.
I missed it the first 3 times I read your formula. =IF(AND(isnumber(SEARCH("*Total",C5)),trim(F6)<"" ),F6*0.03,"") =search() will return a value if it finds that string. Or it'll return an error if it doesn't. ps. Did you really mean *total in your expression. That asterisk doesn't really do anything--excel is using it as a wild card. =IF(AND(isnumber(SEARCH("Total",C5)),trim(F6)<"") ,F6*0.03,"") if you wanted it as a wild card =IF(AND(isnumber(SEARCH("~*Total",C5)),trim(F6)<" "),F6*0.03,"") If you really wanted to look for asterisk-total. Sorry about the delay. jerry wrote: A positive numberical value is in F6 when i get the Value error In the instance when the one cell contains total and the other cell contains something other than "", the appropriate calculation is executed. However, in the instance when the text"total" in not found and yet there is a numberical value(returns true) in the other cell, the value error is displayed. Jerry "Dave Peterson" wrote: What's in it when you get the error? Maybe... =IF(AND(SEARCH("*Total",C5),trim(F6)<""),F6*0.03, "") jerry wrote: F6 can contain either a blank cell ,negative numbers or positive numbers "Dave Peterson" wrote: What's in F6? You may want to use an empty cell and try: =isnumber(f6) And I'd stay away from putting a space character in the cell. It usually makes for trouble later on. I'd use: =IF(AND(SEARCH("*Total",C5),F6<""),F6*0.03,"") jerry wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Thanks a lot Ron and Dave. have a great one!
Jerry "Ron Rosenfeld" wrote: On Fri, 30 Sep 2005 14:25:03 -0700, "jerry" wrote: I am trying to make sure that the row, that meets two criteria return true and then multiply by a percentage. =IF(AND(SEARCH("*Total",C5),F6<" "),F6*0.03," ") this returns a VALUE error. what am i doing wrong, can i not use the search funtion within an IF AND stmt thanks Jerry SEARCH is probably returning the VALUE error by not finding Total in C5. Use this instead: ISNUMBER(SEARCH("*Total",C5)) Also, you probably want to use null strings rather than spaces in checking F6; and also in returning the value_if_false from this function; so: =IF(AND(ISNUMBER(SEARCH("*Total",C5)),F6<""),F6*0 .03,"") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count function problem | Excel Worksheet Functions | |||
Lookup function problem (kg) | Excel Worksheet Functions | |||
Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
Problem adding a range using Sumif function. | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions |