# IF Statement Between a Value Range

1. ## IF Statement Between a Value Range

Hi,

This is hard for me to articulate, so hopefully someone will understand what I'm trying to do.

I'm attempting to construct a formula in Excel, that basically says: IF the value is between these two numbers, multiple them by this percentage, or if the value is between these numbers, multiple it by this percentage.

So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.

I've tried a formula similar to: IF(C1 (Rng(A3:A2), C1*B3) ---- Meaning that if the number is between 10,000,000 and 5,000,000, multiple the number by 10%.

Anyway, it doesn't seem to work and I think I'm overcomplicating this!

HELP!

Thanks  Register To Reply

2. So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.

=if(and(C1<A3,C1>A2),C1*.1,if(and(C1<A2,C1>A1),C1*.05,what do you want to do if it meets neither condition?))

Also, I purposely interpeted your between 10,000 and 5,000 as not including either number. You may want to chnage < to <= or > to >=.  Register To Reply

3. You could try this

=IF(C1>=A3,C1*B3,IF(C1>=A2,C1*B3,IF(C1>=A1,C1*B2,IF(C1<A1,C1*0))))  Register To Reply

4. This problem has been solved a zillion of times on the Net...  Register To Reply

5. Originally Posted by arthurbr
This problem has been solved a zillion of times on the Net...
perhaps you'd like to provide a link to a relevant example?  Register To Reply

Arthur,

I've scoured the net for this. If you could provide a link, I'd appreciate it.

Thanks Duane & Chippy....

How would this work... If B8 is less than A2, it should be multiplied by C2. If B8 is between A3 & A2, it should be multiplied by C3. If B8 is between A4 & A3, it should be multiplied by C4. If B8 is between A5 & A4, it should be multiplied by C5. If B8 is between A5 & A6, it should be multiplied by C6. If B8 is greater than A6, it should be multiplied by C6.

These are the cell values:

A2 = 500
A3 = 1000
A4 = 1500
A5 = 2500
A6 = 3000

C2 = .20
C3 = .25
C4 = .30
C5 = .35
C6 = .40

B8 = VALUE X  Register To Reply

7. Put a zero in A1 and shift your values from C2:C6 to C1:C5 and then use

=LOOKUP(B8,A1:C5)*B8  Register To Reply

8. That didn't seem to work.

Anyone else have any ideas?  Register To Reply

9. Hi smperkin,

Is this any good?

arthurbr - This problem has been solved a zillion of times on the Net...

Make a note of the link!  Register To Reply

10. Originally Posted by smperkin
That didn't seem to work.

Anyone else have any ideas?
oldchippy has provided a very good example of how this would work. If you still can't make it happen then please give details of why it "didn't seem to work". Wrong result, no result, error value etc.  Register To Reply

11. Got it! Thank you. I am obviously not excel savvy but oldchippy's example helped me figure it out.  Register To Reply

12. GOT IT!!!

Thanks everyone.

I really appreciate it.  Register To Reply

13. Glad to help smperkin and niceguyty - thanks for feedback  Register To Reply