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!!!

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


"Teri" wrote:

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!!!

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:

If Cell X is greater than .10, but less than .1499, then 97%

means that Teri wants 98% for less than 0.1

Perhapd it would be better written that other way round:

=IF(A10.1499,"Too Big",IF(A10.1,97%,IF(A10,98%,100%)))

--
Regards,

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


with @tiscali.co.uk


"Toppers" wrote in message
...
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


"Teri" wrote:

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!!!

"Sandy Mann" wrote...
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:

If Cell X is greater than .10, but less than .1499, then 97%

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(A10.1499,"Too Big",IF(A10.1,97%,IF(A10,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")))

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

"Toppers" wrote:

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


"Teri" wrote:

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!!!

"Harlan Grove" wrote in message
oups.com
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(A10.1499,"Too Big",IF(A1=0.1,97%,IF(A10,98%,100%)))

Even then there is a problem - ironically enough the same problem that I was
pointing out to Toppers - with A10.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


with @tiscali.co.uk


"Harlan Grove" wrote in message
oups.com...
"Sandy Mann" wrote...
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:

If Cell X is greater than .10, but less than .1499, then 97%

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(A10.1499,"Too Big",IF(A10.1,97%,IF(A10,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")))