Excel Forum

Complex IF AND Formula Nested Upload Images Link
Darrin Hebert
5 months ago

I'm trying to set an adjustment percentage, based on an input value.  The adjustment levels are from 100%, 80%, 50%, etc.

in doing this I've been using a nested IF(AND() formula for each range of input values.  My problem is the formula entered correctly and works for only the first two ranges, otherwise the cell result is "False".  Here is the actual formula:

[SEE BELOW]

Range of adjustments:

Cell B2 is a value I enter between 0 and 100+.
When B2=100 or greater I get 1
When B2=90-99 I get .8
When B2=Anything else I get False

QRS

LowHigh%
171001
1890990.8
1980890.6
2040790.5
2120390.3
220190

I think my issue is in my <= or <= operators, but everything I've tried to change, doesn't seem to be working.

Is there anyone here that would be willing to point me in the right direction, or suggest a different way of trying to do this?

Darrin Hebert
5 months ago

Kevin Robertson
5 months ago

XLOOKUP

Richard Rost
5 months ago

That was the original formula. It was so wide it was pushing the table margins. LOL

Darrin Hebert
5 months ago

Thanks Kevin & Richard.... Appr. the quick response and helping me. I like the XLookup option a bit more.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Excel Forum Comments