If Statement tiered bucket help

[PghYoPro]

Hello,

I have the following data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Units[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Price per Unit[/TD]
[/TR]
[TR]
[TD]9130[/TD]
[TD]7063[/TD]
[TD]7600[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7601[/TD]
[TD]8000[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8001[/TD]
[TD]9200[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9201[/TD]
[TD][/TD]
[TD]55[/TD]
[/TR]
</tbody>[/TABLE]

The total price paid would be the overage in each bucket * the price per unit. How can I write this using a single IF statement?

Thanks

 

[Joe4]

What if it is less than the minimum number you have shown (7063)?

 

[PghYoPro]

Hi Joe, if the number of units is less than 7063 the price per unit would be $0. The solution in this problem would be $95,970. Not sure if that was clear

 

[Joe4]

The solution in this problem would be $95,970.

I don't see how you arrive at that number.
Can you break it down for us?

 

[C with no eyes]

I am assuming the Units title is in cell A1

<b3),d2,if(and(a2><b4),d3,if(and(a2><b5),d4,if(a2>=IF(AND(A2 > =B2,A2<b3),d2,if(and(a2> =B3,A2 < B4),D3,IF(AND(A2 > =B4,A2 < B5),D4,IF(A2 > =B5,D5,"Units are below valid minimum"))))

This returns the price per unit.

I was unsure what you meant by overage (whether you meant just the final difference is to be calculated or a cumulative difference of the respective prices). So you'll need to put the calculation that you want it to do around the 4 places that column D is referenced.</b3),d2,if(and(a2></b5),d4,if(a2></b4),d3,if(and(a2></b3),d2,if(and(a2>

 

[PghYoPro]

No problem, I'm getting $95,940 by taking the amount in each bucket * the value then adding the totals for each bucket to reach the grand total.

7064-7600: 536 units * $40 = $21,440
7600-8000: 400 units * $45 = $18,000
8000-9201: 1,130 units * $50 = $56,500

 

[Joe4]

I get 95980, because 7064 - 7600 is actually 537 units, not 536 (think of it this way, is 7064 - 7064 one unit or none)?
So, if you MIN/MAX/PRICE grid was in cells A1:C6 (where row 1 is the header row), and your number is in cell E2, this formula returns what you are looking for:

=(MAX(MIN(B3-A3+1,$E$2-A3+1),0)*C3)
+(MAX(MIN(B4-A4+1,$E$2-A4+1),0)*C4)
+(MAX(MIN(B5-A5+1,$E$2-A5+1),0)*C5)
+(IF(E2>=A6,$E$2-A6+1,0)*C6)

 

[PghYoPro]

Great, thanks! Thanks for the lesson in counting, too

 

[Eric W]

Another option:

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Units[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Min[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Max[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Price per Unit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Relative change[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9130[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7063[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7600[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7601[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Total Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]95970[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=SUMPRODUCT(A2-B2:B5,E2:E5,--(A2>B2:B5))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=D3-D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=D4-D3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=D5-D4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]