If Statement tiered bucket help

[PghYoPro]

Hello,

I have the following data:

Units	Min	Max	Price per Unit
9130	7063	7600	40
	7601	8000	45
	8001	9200	50
	9201		55

<tbody>
</tbody>

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
1	Units	Min	Max	Price per Unit	Relative change
2	9130	7063	7600	40	40
3		7601	8000	45	5
4	Total Price	8001	9200	50	5
5	95970	9201		55	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A5 =SUMPRODUCT(A2-B2:B5,E2:E5,--(A2>B2:B5)) E3 =D3-D2 E4 =D4-D3 E5 =D5-D4 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>