aNOTHer troublesome equation

[Micayla Bergen]

i dont know what i have too many of or not enough of in this equation but
something is awry
=IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",=IF(B9=1,(J9=0,"Stock not rated",IF(J9<3,"Sell - low rating","")),))
thanks anyone

[Bryan Hessey]

It appears to say :
= if a9 is cmt divs or fund (or J9 is blank) use ""
else (spare =) if (b9=1 ...do-this-thing,.... else , nothing))

where do-this-thing is (J9=0,"Stock not rated",IF(J9<3,"Sell - low rating",""))

which doesn't make sense to me.

i dont know what i have too many of or not enough of in this equation but
something is awry
=IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",=IF(B9=1,(J9=0,"Stock not rated",IF(J9<3,"Sell - low rating","")),))
thanks anyone

[Max]

Try this rectification. But you have to test whether it returns correctly
based on your test values / expected returns

=IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",IF(OR(B9=1,J9=0)
,"Stock not rated",IF(J9<3,"Sell - low rating","")))

The error was in this part: .. =IF(B9=1,(J9=0, ..
which was rectified (with some guesswork) to read as:
.... IF(OR(B9=1,J9=0) ...
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Micayla Bergen" <[email protected]> wrote in message
news:[email protected]...
> i dont know what i have too many of or not enough of in this equation but
> something is awry
>
=IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",=IF(B9=1,(J9=0,"
Stock not rated",IF(J9<3,"Sell - low rating","")),))
> thanks anyone

[Micayla Bergen]

it came back as blank for all of the lines even when it shouldnt have

"Max" wrote:

> Try this rectification. But you have to test whether it returns correctly
> based on your test values / expected returns
>
> =IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",IF(OR(B9=1,J9=0)
> ,"Stock not rated",IF(J9<3,"Sell - low rating","")))
>
> The error was in this part: .. =IF(B9=1,(J9=0, ..
> which was rectified (with some guesswork) to read as:
> .... IF(OR(B9=1,J9=0) ...
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Micayla Bergen" <[email protected]> wrote in message
> news:[email protected]...
> > i dont know what i have too many of or not enough of in this equation but
> > something is awry
> >
> =IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",=IF(B9=1,(J9=0,"
> Stock not rated",IF(J9<3,"Sell - low rating","")),))
> > thanks anyone
>
>
>

[Bryan Hessey]

Max's =IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",IF(OR(B9=1,J9=0),"Stock not rated",IF(J9<3,"Sell - low rating",""))) works for me, but remember it is on row 9

it came back as blank for all of the lines even when it shouldnt have

"Max" wrote:

> Try this rectification. But you have to test whether it returns correctly
> based on your test values / expected returns
>
> =IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",IF(OR(B9=1,J9=0)
> ,"Stock not rated",IF(J9<3,"Sell - low rating","")))
>
> The error was in this part: .. =IF(B9=1,(J9=0, ..
> which was rectified (with some guesswork) to read as:
> .... IF(OR(B9=1,J9=0) ...
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Micayla Bergen" <[email protected]> wrote in message
> news:[email protected]...
> > i dont know what i have too many of or not enough of in this equation but
> > something is awry
> >
> =IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",=IF(B9=1,(J9=0,"
> Stock not rated",IF(J9<3,"Sell - low rating","")),))
> > thanks anyone
>
>
>

[Max]

Maybe you can post some test data (what's does A9, J9 and B9 contain) for
those instances which return blank when it shouldn't be the case ..

Here's some clarifications on the returns by the rectified formula as it
stands (but remember that the rectification contains some guesswork as
stated)

If A9 is blank, B9 contains: 1, J9 is not blank,
result will be: Stock not rated

If A9 is blank, B9 does not contain: 1, J9 contains: 1 (or 2),
result will be: Sell - low rating

If A9 contains either: cmt, divs, funds, result will be: blank
(irrespective of what J9 or B9 may contain)

If J9 is blank (or contains a null string: ""), result will be: blank
(irrespective of what A9 or B9 may contain)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Micayla Bergen" <[email protected]> wrote in message
news:[email protected]...
> it came back as blank for all of the lines even when it shouldnt have

[Micayla Bergen]

firstly the spreadsheet is to assist w future portfolio distributions, so the
cell that this formula is in - Q - tells us if we are overweighted in any
type of share.
So if A9 is not empty then B9 will always equal 1.
what i want the formula in Q to say is if A9 contains cmt or divs or fund
then Q should be blank, but if A9 contains something but not the above then
it should look in J and if J contains a 1 or a 2 then Q should say 'sell -
low rating' or if J contains 0 then Q should say 'stock not rated'. however
cmt divs and fund have no rating so i dont want that to come up if they are
in A.
thanks

"Max" wrote:

> Maybe you can post some test data (what's does A9, J9 and B9 contain) for
> those instances which return blank when it shouldn't be the case ..
>
> Here's some clarifications on the returns by the rectified formula as it
> stands (but remember that the rectification contains some guesswork as
> stated)
>
> If A9 is blank, B9 contains: 1, J9 is not blank,
> result will be: Stock not rated
>
> If A9 is blank, B9 does not contain: 1, J9 contains: 1 (or 2),
> result will be: Sell - low rating
>
> If A9 contains either: cmt, divs, funds, result will be: blank
> (irrespective of what J9 or B9 may contain)
>
> If J9 is blank (or contains a null string: ""), result will be: blank
> (irrespective of what A9 or B9 may contain)
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Micayla Bergen" <[email protected]> wrote in message
> news:[email protected]...
> > it came back as blank for all of the lines even when it shouldnt have
>
>
>

[Max]

Try this slight modification,
which seems to deliver the results as specified:

=IF(OR(ISNUMBER(SEARCH({"cmt","divs","fund"},A9)),J9=""),"",IF(J9=0,"Stock
not rated",IF(AND(J9>0,J9<3),"Sell - low rating","")))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Micayla Bergen" <[email protected]> wrote in message
news:[email protected]...
> firstly the spreadsheet is to assist w future portfolio distributions, so
the
> cell that this formula is in - Q - tells us if we are overweighted in any
> type of share.
> So if A9 is not empty then B9 will always equal 1.
> what i want the formula in Q to say is if A9 contains cmt or divs or fund
> then Q should be blank, but if A9 contains something but not the above
then
> it should look in J and if J contains a 1 or a 2 then Q should say 'sell -
> low rating' or if J contains 0 then Q should say 'stock not rated'.
however
> cmt divs and fund have no rating so i dont want that to come up if they
are
> in A.
> thanks
>
> "Max" wrote:
>
> > Maybe you can post some test data (what's does A9, J9 and B9 contain)
for
> > those instances which return blank when it shouldn't be the case ..
> >
> > Here's some clarifications on the returns by the rectified formula as it
> > stands (but remember that the rectification contains some guesswork as
> > stated)
> >
> > If A9 is blank, B9 contains: 1, J9 is not blank,
> > result will be: Stock not rated
> >
> > If A9 is blank, B9 does not contain: 1, J9 contains: 1 (or 2),
> > result will be: Sell - low rating
> >
> > If A9 contains either: cmt, divs, funds, result will be: blank
> > (irrespective of what J9 or B9 may contain)
> >
> > If J9 is blank (or contains a null string: ""), result will be: blank
> > (irrespective of what A9 or B9 may contain)
> >
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Singapore, GMT+8
> > xdemechanik
> > http://savefile.com/projects/236895
> > --
> > "Micayla Bergen" <[email protected]> wrote in
message
> > news:[email protected]...
> > > it came back as blank for all of the lines even when it shouldnt have
> >
> >
> >