- 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 3:17 pm to TDawg1313
I have Office Professional Plus 16. However, the IFS function is not available in this version of Excel. I actually read about IFS prior to posting on TD & was surprised to learn IFS is not in my version for some reason.
This post was edited on 6/27/20 at 3:18 pm
Posted on 6/27/20 at 5:16 pm to LSU
I guess it's not too complicated to switch it to the nested IF statement rather than using IFS. Just need to modify it a bit.
This is the same formula as above, but not using IFS.
=if(AND(A1>=0,A1<=6),15,
if(AND(A1>6,A1<=16),15+1.25*(A1-6),
if(AND(A1>16,A1<=26),27.5+1.5*(A1-16),
if(AND(A1>26,A1<=36),42.5+1.75*(A1-26),
if(A1>36,60+2*(A1-36),0)))))
This is the same formula as above, but not using IFS.
=if(AND(A1>=0,A1<=6),15,
if(AND(A1>6,A1<=16),15+1.25*(A1-6),
if(AND(A1>16,A1<=26),27.5+1.5*(A1-16),
if(AND(A1>26,A1<=36),42.5+1.75*(A1-26),
if(A1>36,60+2*(A1-36),0)))))
This post was edited on 6/27/20 at 5:52 pm
Posted on 6/27/20 at 7:17 pm to TDawg1313
Thanks. I was trying it in a much more complicated manner. Appreciate it!
Posted on 6/27/20 at 7:46 pm to LSU
quote:
Thanks. I was trying it in a much more complicated manner. Appreciate it!
No problem
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)
Posted on 6/27/20 at 10:23 pm to CubsFanBudMan
quote:
If you like tables and vlookups, you can do a table on sheet2 with the following:
IMO this will be the route you would want to go. Makes it a lot easier to fix if/when prices or ranges change
Posted on 6/27/20 at 10:29 pm to Weekend Warrior79
quote:
IMO this will be the route you would want to go. Makes it a lot easier to fix if/when prices or ranges change
Agreed. That's the best solution.
Posted on 6/28/20 at 1:30 am to TDawg1313
Never use VLOOKUP. It's a volatile function and is calculated EVERY time something in the spreadsheet triggers a recalculation, regardless of whether or not the VLOOKUP function is actually in the chain of dependencies that Excel builds so it only recalculates the affected cells. Volatile functions don't have regard for your sill dependency chain and Large spreadsheets will grind to a halt with dynamic functions littered through them.
Use the same table idea, but use INDEX and MATCH functions to replicate the VLOOKUP function and avoid unnecessary recalculations of volatile functions.
Use the same table idea, but use INDEX and MATCH functions to replicate the VLOOKUP function and avoid unnecessary recalculations of volatile functions.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News