Conditional Formula

[BrandonProcell]

I'm not sure if I'm in the right place or if what I'm wanting to do is even possible, but if anyone could help I'd really appreciate it.

I'm trying to have a cell return a value for a range of numbers.

--B2 is where I would input a number.
--If B2 is between 1 and 99,999, then B4 should return "100%".
--If B2 is between 100,000 and 117,999, then B4 should return "99%".
--If B2 is between 118,000 and 135,999, then B4 should return "98%".
--If B2 is between 136,000 and 171,999, then B4 should return "96%".
--And so on... until if B2 is 1,000,000 or higher, B4 should return "50%".

So for every 18,000 added to 100,000, "1%" is subtracted from the previous % all the way down to "50%", which is returned when anything 1,000,000 or above in entered into B2.

So, for example:

--If I enter 532,550 in B2, B4 should return "76%".
--If I enter 285,321 in B2, B4 should return "90%".
--If I enter 67,500 in B2, B4 should return "100%".
--If I enter 2,343,500 in B2, B4 should return "50%".

I hope I was clear enough. I can really use the help. Thanks in advance!

[NBVC]

Try something like:

=LOOKUP(B2,{0,100000,118000,136000,1000000},{1,0.99,0.98,0.96,0.5})

format the result as Number|Percentage

Note, you can insert additional lower bounds in the first array, and the corresponding values in the same position in the second array... the first array must remain in ascending order.

[BrandonProcell]

Thanks NBVC!

Your suggestion worked perfectly! You're Awesome!

[NBVC]

Wondering if this is a shorter way...?

=IF(B2<100000,1,1-((CEILING(B2-99999,18000))/18000)/100)

[BrandonProcell]

Wondering if this is a shorter way...?

=IF(B2<100000,1,1-((CEILING(B2-99999,18000))/18000)/100)

Yes, that works and is far less complicated. I added another conditional IF to limit it to 50% at 1,000,000. So now it's exactly what I was looking for. Thanks so much!

=IF(B2<1000000,IF(B2<100000,1,1-((CEILING(B2-100000,18000))/18000)/100),0.5)

[NBVC]

Notice that I slightly updated my last offering, as it was off when you entered the actual lower boundaries... just replace the 100000 in the CEILING() function to 99999