IF Statement Between a Value Range

**smperkin** · 01-13-2007, 12:37 PM

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

**duane** · 01-13-2007, 12:57 PM

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 >=.

**oldchippy** · 01-13-2007, 01:15 PM

You could try this

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

**arthurbr** · 01-13-2007, 01:15 PM

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

**daddylonglegs** · 01-13-2007, 01:18 PM

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?

**smperkin** · 01-13-2007, 01:20 PM

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

**daddylonglegs** · 01-13-2007, 01:25 PM

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

=LOOKUP(B8,A1:C5)*B8

**smperkin** · 01-13-2007, 02:33 PM

That didn't seem to work.

Anyone else have any ideas?

**oldchippy** · 01-13-2007, 02:54 PM

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!

**daddylonglegs** · 01-13-2007, 04:12 PM

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.