Pricing Per Unit Formula Help

[Foyer8888]

Hello MrExcel Community,

I am looking for some assistance on building a formula to calculate the price per unit based on a tiered quantity.

 

[rpaulson]

May not be the best option but it works.
Formulas:
D18 =MIN($C$4,C18)
D19 =MIN(C19,$C$4-D18)
D20 =MIN(C20,$C$4-D19-D18)
D21 =MIN(C21,$C$4-D20-D19-D18)
D22 =MIN(C22,$C$4-D21-D20-D19-D18)

 

[Foyer8888]

May not be the best option but it works.
Formulas:
D18 =MIN($C$4,C18)
D19 =MIN(C19,$C$4-D18)
D20 =MIN(C20,$C$4-D19-D18)
D21 =MIN(C21,$C$4-D20-D19-D18)
D22 =MIN(C22,$C$4-D21-D20-D19-D18)

Thank you for the reply. Perhaps I am doing something wrong, but I entered the formulas exactly as you have listed and am not getting the same results. It seems to work up for 256, but once I get to the second tier and exceed the 1024, the amount continues to add in the Tier 2 line instead of moving to the next tier.

 

[Foyer8888]

Thank you for the reply. Perhaps I am doing something wrong, but I entered the formulas exactly as you have listed and am not getting the same results. It seems to work up for 256, but once I get to the second tier and exceed the 1024, the amount continues to add in the Tier 2 line instead of moving to the next tier.

 

[Foyer8888]

View attachment 97934

Ok, apologies for the reply, I realized the change required. The end parenthesis should be after the $C$4. I made this change and it works. Thank you for your help.

 

[rpaulson]

glad to hear

 

[smozgur]

Ok, apologies for the reply, I realized the change required. The end parenthesis should be after the $C$4. I made this change and it works. Thank you for your help.

Could you please post the final solution, then it is perfectly fine to mark your own answer as the solution as it will help future readers.

 

[Foyer8888]

Could you please post the final solution, then it is perfectly fine to mark your own answer as the solution as it will help future readers.

Here is the corrected solution.

D18 =MIN($C$4,C18)

D19 =MIN(C19,$C$4)-D18

D20 =MIN(C20,$C$4)-D19-D18

D21 =MIN(C21,$C$4)-D20-D19-D18

D22 =MIN(C22,$C$4)-D21-D20-D19-D18