- 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
re: Excel Nested IF Statement Help Needed
Posted on 6/27/20 at 8:58 pm to LSU
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
Popular
Back to top
Follow TigerDroppings for LSU Football News