multiple if statements in same cell

Hawkwin

New Member
Joined
Dec 2, 2009
Messages
6
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.
 
Last edited:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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

New Member
Joined
Dec 2, 2009
Messages
6
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

Legend
Joined
Feb 18, 2002
Messages
13,690
You do not need the ANDs or a Lookup
Code:
=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

New Member
Joined
Dec 2, 2009
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,646
Messages
5,597,352
Members
414,138
Latest member
Ankitjainkaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top