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

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

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
>

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

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
>

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

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

On Fri, 30 Sep 2005 14:25:03 -0700, "jerry" <jerry@discussions.microsoft.com>
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

Thanks a lot Ron and Dave. have a great one!
Jerry

"Ron Rosenfeld" wrote:

> On Fri, 30 Sep 2005 14:25:03 -0700, "jerry" <jerry@discussions.microsoft.com>
> 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
>