Help with a Formula

carl · 02-10-2006, 02:10 PM

Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the threshold,
the formula does not return "check". For example in:

IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
does not equal "sell" and M1232-K1232 equals -.25 the formula does not return
"check".

Here's the entire formula:

=IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K1232>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-1)); "Check";"")

Thank you in advance.

Duke Carey · 02-10-2006, 02:30 PM

Try rounding, i.e.,

IF(I1232="sell",round(K1232-L1232),round(M1232-K1232))<=-0.25)

"carl" wrote:

> Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the threshold,
> the formula does not return "check". For example in:
>
> IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
> does not equal "sell" and M1232-K1232 equals -.25 the formula does not return
> "check".
>
> Here's the entire formula:
>
> =IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K1232>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-1)); "Check";"")
>
> Thank you in advance.

Duke Carey · 02-10-2006, 02:35 PM

Try rounding, i.e.,

IF(I1232="sell",round(K1232-L1232),round(M1232-K1232))<=-0.25)

"carl" wrote:

> Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the threshold,
> the formula does not return "check". For example in:
>
> IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
> does not equal "sell" and M1232-K1232 equals -.25 the formula does not return
> "check".
>
> Here's the entire formula:
>
> =IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K1232>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-1)); "Check";"")
>
> Thank you in advance.

Bob Phillips · 02-10-2006, 02:55 PM

I think Duke means

IF(I1232="sell",round(K1232-L1232,0),round(M1232-K1232,0))<=-0.25)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Duke Carey" <[email protected]> wrote in message
news:[email protected]...
> Try rounding, i.e.,
>
> IF(I1232="sell",round(K1232-L1232),round(M1232-K1232))<=-0.25)
>
>
> "carl" wrote:
>
> > Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the
threshold,
> > the formula does not return "check". For example in:
> >
> > IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
> > does not equal "sell" and M1232-K1232 equals -.25 the formula does not
return
> > "check".
> >
> > Here's the entire formula:
> >
> >
=IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K123
2>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1
232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=
20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="se
ll";K1232-L1232;M1232-K1232)<=-1)); "Check";"")
> >
> > Thank you in advance.

Duke Carey · 02-10-2006, 03:10 PM

I have to correct Bob's correction or my error

IF(I1232="sell",round(K1232-L1232,2),round(M1232-K1232,2))<=-0.25)

I hope we're all done with this subject

"Bob Phillips" wrote:

> I think Duke means
>
> IF(I1232="sell",round(K1232-L1232,0),round(M1232-K1232,0))<=-0.25)
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Duke Carey" <[email protected]> wrote in message
> news:[email protected]...
> > Try rounding, i.e.,
> >
> > IF(I1232="sell",round(K1232-L1232),round(M1232-K1232))<=-0.25)
> >
> >
> > "carl" wrote:
> >
> > > Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the
> threshold,
> > > the formula does not return "check". For example in:
> > >
> > > IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
> > > does not equal "sell" and M1232-K1232 equals -.25 the formula does not
> return
> > > "check".
> > >
> > > Here's the entire formula:
> > >
> > >
> =IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K123
> 2>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1
> 232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=
> 20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="se
> ll";K1232-L1232;M1232-K1232)<=-1)); "Check";"")
> > >
> > > Thank you in advance.
>
>
>

Duke Carey · 02-10-2006, 03:15 PM

OK, I wasn't done:

shorter version:

ROUND(IF(I1232="sell",K1232-L1232,M1232-K1232),2)<=-0.25

"Duke Carey" wrote:

> I have to correct Bob's correction or my error
>
> IF(I1232="sell",round(K1232-L1232,2),round(M1232-K1232,2))<=-0.25)
>
> I hope we're all done with this subject
>
>
> "Bob Phillips" wrote:
>
> > I think Duke means
> >
> > IF(I1232="sell",round(K1232-L1232,0),round(M1232-K1232,0))<=-0.25)
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Duke Carey" <[email protected]> wrote in message
> > news:[email protected]...
> > > Try rounding, i.e.,
> > >
> > > IF(I1232="sell",round(K1232-L1232),round(M1232-K1232))<=-0.25)
> > >
> > >
> > > "carl" wrote:
> > >
> > > > Hi. For some reason when "K1232-L1232;M1232-K1232" is equal to the
> > threshold,
> > > > the formula does not return "check". For example in:
> > > >
> > > > IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25). In this case, if I1232
> > > > does not equal "sell" and M1232-K1232 equals -.25 the formula does not
> > return
> > > > "check".
> > > >
> > > > Here's the entire formula:
> > > >
> > > >
> > =IF(OR(AND(K1232<2;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.25);AND(K123
> > 2>=2;K1232<=5;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.4);AND(K1232>5;K1
> > 232<=10;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.5);AND(K1232>10;K1232<=
> > 20;IF(I1232="sell";K1232-L1232;M1232-K1232)<=-0.8);AND(K1232>20;IF(I1232="se
> > ll";K1232-L1232;M1232-K1232)<=-1)); "Check";"")
> > > >
> > > > Thank you in advance.
> >
> >
> >