IF Statement---ARGGGH!!!

[Guest]

Can somebody PLEASE help me write this as an IF statement?

If Cell X is less than 0, then 100%
If Cell X is greater than 0, but less than .0999, then 98%
If Cell X is greater than .10, but less than .1499, then 97%

I'm going nuts!!!

 

[Guest]

Format cell as %:

=IF(A1<0,1,IF(A1<0.0999,0.98,IF(A1<0.1499,0.97,??)))

and if x >= 0.1499 ????

The above satisfies:

If Cell X is less than 0, then 100%
If Cell X is less than 0.10, then 98%
If Cell X is greater less than 0.1499, then 97%

HTH

 

[Sandy Mann]

Toppers,

I know that the OP gave the values 0 & 0.0999 but if A1 was the result of a
calculation then there is no guarantee that it could not hold 0.09995 and
the way I read the OP's line:

means that Teri wants 98% for less than 0.1

Perhapd it would be better written that other way round:

=IF(A1>0.1499,"Too Big",IF(A1>0.1,97%,IF(A1>0,98%,100%)))

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Harlan Grove]

I know that the OP gave the values 0 & 0.0999 but if A1 was the
result of a calculation then there is no guarantee that it could
not hold 0.09995 and the way I read the OP's line:


means that Teri wants 98% for less than 0.1

So far I agree with your assessment, but the converse is that the OP
wants 97% for greater than OR EQUAL TO 0.1.

Perhapd it would be better written that other way round:

=IF(A1>0.1499,"Too Big",IF(A1>0.1,97%,IF(A1>0,98%,100%)))

....

This is inconsistent with the converse of your assessment since A1
would need to be strictly greater than 0.1 for the formula to return
97%. Given that, why not a simple lookup?

=LOOKUP(A1,{-1E300;0;0.1;0.15},{1;0.98;0.97;"out of range"})

or sticking with IFs,

=IF(A1<0,100%,IF(A1<0.1,98%,IF(A1<0.15,97%,"out of range")))

 

[Guest]

Thank you, thank you, thank you!!! It worked perfectly

 

[Sandy Mann]

This is inconsistent with the converse of your assessment since A1
would need to be strictly greater than 0.1 for the formula to return
97%.

Yes, as someone around here would say I goofed <g> it should have been:

=IF(A1>0.1499,"Too Big",IF(A1>=0.1,97%,IF(A1>0,98%,100%)))

Even then there is a problem - ironically enough the same problem that I was
pointing out to Toppers - with A1>0.1499

On the other hand it depends on what the OP meant by:

If Cell X is less than 0, then 100%
If Cell X is greater than 0, but less than .0999, then 98%

If Teri intended:

If Cell X is equal to or less than 0 then your IF() formual can be amended
to:

=IF(A1<=0,100%,IF(A1<0.1,98%,IF(A1<0.15,97%,"out of range")))

but the only way that I could get your LOOKUP() to achieve that would be:

=LOOKUP(A1,{-1E+300;0;1E-300;0.1;0.15},{1;1;0.98;0.97;"out of range"})

Unless you know of any other construction?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk