- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Nested IF Statement Help Needed
Posted on 6/27/20 at 1:59 pm
Posted on 6/27/20 at 1:59 pm
I fail at long nested IF statements. Trying to determine cost based on usage. Anyone know what the Excel formula would be with the info below?
Usage amount goes in cell A1. Basically cost goes up $0.25 per gallon for each range.
For example, if usage is 13 gallons, then cost is $23.75. It's $15 for the usage up to 6 gallons & then $1.25 per gallon for the 7 additional gallons.
If usage is 17 gallons, then cost is $29. It's $15 for the usage up to 6 gallons, then $1.25 per gallon for the 10 additional gallons up to 16, plus $1.50 for the 17th gallon that's in the 16-26 gallon range.
If A1 is 0-6, then it's $15
If A1 >6 & <=16, then it's $15 plus $1.25 per gallon from 6-16
If A1 >16 & <=26, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26
If A1 >26 & <=36, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26 plus $1.75 per gallon from 26-36
If A1 >36, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26 plus $1.75 per gallon from 26-36 plus $2 per gallon over 36
Usage amount goes in cell A1. Basically cost goes up $0.25 per gallon for each range.
For example, if usage is 13 gallons, then cost is $23.75. It's $15 for the usage up to 6 gallons & then $1.25 per gallon for the 7 additional gallons.
If usage is 17 gallons, then cost is $29. It's $15 for the usage up to 6 gallons, then $1.25 per gallon for the 10 additional gallons up to 16, plus $1.50 for the 17th gallon that's in the 16-26 gallon range.
If A1 is 0-6, then it's $15
If A1 >6 & <=16, then it's $15 plus $1.25 per gallon from 6-16
If A1 >16 & <=26, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26
If A1 >26 & <=36, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26 plus $1.75 per gallon from 26-36
If A1 >36, then it's $15 plus $1.25 per gallon from 6-16 plus $1.50 per gallon from 16-26 plus $1.75 per gallon from 26-36 plus $2 per gallon over 36
2
Posted on 6/27/20 at 2:50 pm to LSU
Excel created a new IFS function in Excel 2016. Hopefully you have that function because it will make this a lot easier.
This formula using that IFS function should do it. I hard coded the starting price within each tier, which isn't really the best practice, but it gets more complicated if you want that to be dynamic as well. Considering you would like this all in one formula, I assumed that it being dynamic to that point isn't necessary.
=IFS(AND(A1>=0,A1<=6),15,AND(A1>6,A1<=16),15+1.25*(A1-6),AND(A1>16,A1<=26),27.5+1.5*(A1-16),AND(A1>26,A1<=36),42.5+1.75*(A1-26),A1>36,60+2*(A1-36))
Here's a more readable format of the same formula.
=IFS(
AND(A1>=0,A1<=6),15
,AND(A1>6,A1<=16),15+1.25*(A1-6)
,AND(A1>16,A1<=26),27.5+1.5*(A1-16)
,AND(A1>26,A1<=36),42.5+1.75*(A1-26)
,A1>36,60+2*(A1-36)
)
This formula using that IFS function should do it. I hard coded the starting price within each tier, which isn't really the best practice, but it gets more complicated if you want that to be dynamic as well. Considering you would like this all in one formula, I assumed that it being dynamic to that point isn't necessary.
=IFS(AND(A1>=0,A1<=6),15,AND(A1>6,A1<=16),15+1.25*(A1-6),AND(A1>16,A1<=26),27.5+1.5*(A1-16),AND(A1>26,A1<=36),42.5+1.75*(A1-26),A1>36,60+2*(A1-36))
Here's a more readable format of the same formula.
=IFS(
AND(A1>=0,A1<=6),15
,AND(A1>6,A1<=16),15+1.25*(A1-6)
,AND(A1>16,A1<=26),27.5+1.5*(A1-16)
,AND(A1>26,A1<=36),42.5+1.75*(A1-26)
,A1>36,60+2*(A1-36)
)
This post was edited on 6/27/20 at 2:55 pm
Posted on 6/27/20 at 8:58 pm to LSU
The thing I love about Excel is that there's more than 1 way to accomplish your goal. Here's a min/max formula for you:
=15+min(12.5,max(0,(A1-6)*1.25))+min(15,max(0,(A1-16)*1.5))+min(17.5,max(0,(A1-26)*1.75))+max(0,(A1-36)*2)
If you like tables and vlookups, you can do a table on sheet2 with the following:
Base;Min;Additional
0;15;0
6;15;1.25
16;27.5;1.5
26;42.5;1.75
36;60;2
and the formula of:
=VLOOKUP(A1,Sheet2!$A$1:$C$6,2,TRUE)+(A1-VLOOKUP(A1,Sheet2!$A$1:$C$6,1,TRUE))*VLOOKUP(A1,Sheet2!$A$1:$C$6,3,TRUE)
=15+min(12.5,max(0,(A1-6)*1.25))+min(15,max(0,(A1-16)*1.5))+min(17.5,max(0,(A1-26)*1.75))+max(0,(A1-36)*2)
If you like tables and vlookups, you can do a table on sheet2 with the following:
Base;Min;Additional
0;15;0
6;15;1.25
16;27.5;1.5
26;42.5;1.75
36;60;2
and the formula of:
=VLOOKUP(A1,Sheet2!$A$1:$C$6,2,TRUE)+(A1-VLOOKUP(A1,Sheet2!$A$1:$C$6,1,TRUE))*VLOOKUP(A1,Sheet2!$A$1:$C$6,3,TRUE)
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News