Page 1
Page 1
Posted by
Message
LSU LSU Fan
Houston
Member since Oct 2003
7923 posts Online

Excel Nested IF Statement Help Needed
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 0 1
TDawg1313 Washington Fan
WA
Member since Jul 2009
12082 posts

re: Excel Nested IF Statement Help Needed
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 post was edited on 6/27 at 2:55 pm 4 0
LSU LSU Fan
Houston
Member since Oct 2003
7923 posts Online

re: Excel Nested IF Statement Help Needed
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 at 3:18 pm 0 0
TDawg1313 Washington Fan
WA
Member since Jul 2009
12082 posts

re: Excel Nested IF Statement Help Needed
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 post was edited on 6/27 at 5:52 pm 2 0
LSU LSU Fan
Houston
Member since Oct 2003
7923 posts Online

re: Excel Nested IF Statement Help Needed
Thanks. I was trying it in a much more complicated manner. Appreciate it! 1 0
TD Sponsor TD Fan
USA
Member since 2001
Thank you for supporting our sponsors
TDawg1313 Washington Fan
WA
Member since Jul 2009
12082 posts

re: Excel Nested IF Statement Help Needed
quote:

Thanks. I was trying it in a much more complicated manner. Appreciate it!

No problem Replies (0)
Replies (0) 0 0
CubsFanBudMan Chicago Cubs Fan
Member since Jul 2008
3807 posts

re: Excel Nested IF Statement Help Needed
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:

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) 3 0
Weekend Warrior79 LSU Fan
Member since Aug 2014
5263 posts

re: Excel Nested IF Statement Help Needed
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 0 0
TDawg1313 Washington Fan
WA
Member since Jul 2009
12082 posts

re: Excel Nested IF Statement Help Needed
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. 0 0
TigerstuckinMS LSU Fan
Member since Nov 2005
26430 posts Online

re: Excel Nested IF Statement Help Needed
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.

Replies (0)
Replies (0) 1 1
Page 1  Page 1 of 1    Follow TigerDroppings for LSU Football News