multiple if statements in same cell

[Hawkwin]

Edit: Using Excel 2003.

I am trying to create a formula where by the result is the multiplication of a cell based on five possble ranges within that cell.

I currently have =if(A1<1500.00,A1*.1).

That works just fine if A1 is 1400.00.

I want it to also =if(AND(A1>1499.99,A1<3000.00),A1*0.15).

That, by itself, works just fine if A1 is 2000.00.

What I can't seem to figure out is how to have them both be in the same cell so that if A1 is 900 sometimes and other times it is 2200, that the formula will work. I need five of these in total to work in the same cell for the following ranges:

0-14999.99
1500.00-2999.99
3000.00-4499.99
4500.00-5999.99
6000.00+

With the corresponding multiplication respectively being:

*.10
*.15
*.20
*.25
*.30

Any help is appreciated.

 

[scottylad2]

Edit: Using Excel 2003.

I am trying to create a formula where by the result is the multiplication of a cell based on five possble ranges within that cell.

I currently have =if(A1<1500.00,A1*.1).

That works just fine if A1 is 1400.00.

I want it to also =if(AND(A1>1499.99,A1<3000.00),A1*0.15).

That, by itself, works just fine if A1 is 2000.00.

What I can't seem to figure out is how to have them both be in the same cell so that if A1 is 900 sometimes and other times it is 2200, that the formula will work. I need five of these in total to work in the same cell for the following ranges:

0-14999.99
1500.00-2999.99
3000.00-4499.99
4500.00-5999.99
6000.00+

With the corresponding multiplication respectively being:

*.10
*.15
*.20
*.25
*.30

Any help is appreciated.

I think an IF and VLOOKUP with dropdown data validation with your values in the the DV list would be the best approach

 

[Hawkwin]

I am sorry but I am ESL - Excel Second Language.

I am completely unfamiliar with the VLOOKUP function. Can you elaborate a bit on how I could use such?

 

[lenze]

You do not need the ANDs or a Lookup

=IF(A1<1500,0.1,IF(A1<3000,0.15,IF(A1<4500,0.2,IF(A1<6000,0.25,0.3))))*A1

lenze

 

[Hawkwin]

I think I found perhaps an even simplier way around it.

If I add a hidden column with a single formula per row, I can then sum the results of that formula to the cell I want.

For example, hidden column:
=IF(D9<1500,D9*0.1)
=IF(AND(D9>1499.99,D9<3000),D9*0.15)
=IF(AND(D9>2999.99,D9<4500),D9*0.2)
=IF(AND(D9>4499.99,D9<6000),D9*0.25)
=IF(D9>5999.99,D9*0.3)

Output cell:
=SUM(F9:F13)

Output of the hidden column looks like (using 2400):

FALSE
260
FALSE
FALSE
FALSE

Output to the Output cell is simply: 260

Anyone have any other ideas? I would like to avoid the additional column if possible but this does seem to work.

 

[Hawkwin]

You do not need the ANDs or a Lookup

=IF(A1<1500,0.1,IF(A1<3000,0.15,IF(A1<4500,0.2,IF(A1<6000,0.25,0.3))))*A1

lenze

Outstanding and simplistic, thanks!!!