if Function problem

J

jerry

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
 
D

Dave Peterson

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,"")
 
D

Dave Peterson

What's in it when you get the error?

Maybe...
=IF(AND(SEARCH("*Total",C5),trim(F6)<>""),F6*0.03,"")
 
J

jerry

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
 
J

jerry

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
D

Dave Peterson

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.
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
Click to expand...
 
Top