Calculating inconsistent data/formulas?

[greddy2die]

Since i'm new to excel I'm not that sure if there is any type of formula(s) that would be able to easily calculate costs basis the below parameters.

Calculating the "units" isnt the hard part for me, i've got that done already. It's finding a formula to calculate the costs once I have the "units"

For example, if the units total 283.89 then

first 100 units = 1150.93
next 183.89 units x 8.15 = 1,498.70
total is: 2649.63



If the units total 383.89 then
first 100 units = 1150.93
next 200 units x 8.15 = 1,630
final 83.89 units x 4.34 = 364.08
total is: 3,145.01


so on & so forth

 

[Joe4]

Re: more help needed - calculating inconsistent data/formulas?

Can you post all the different levels, and price associated with each level?

 

[joeu2004]

The correct answer depends on how specific you want to be about rounding.

Ostensibly, one of the following formulas might work for you.



Set up the data in A1:B7 and C2. Enter the indicated formulas in C3 and C4, and copy C4 into C5:C7.

Enter the formulas in B10 and B11. The following are easier to copy-and-paste:

B10: =ROUND($B$2 + SUMPRODUCT((B9>$A$2:$A$7)*(B9-$A$2:$A$7), $C$2:$C$7), 2)
B11: =ROUND($B$2 + SUMPRODUCT(ROUND((B9>$A$2:$A$7)*(B9-$A$2:$A$7)*$C$2:$C$7,2)), 2)

Enter the number of units to ship (372.19) into B9.

Note the $0.01 difference in B10 and B11.

The issue is: if units are non-integers (with 2 decimal places), the products might have 4 decimal places. The sum must be rounded to 2 decimal places.

For "total 1", we round to 2 decimal places are adding all products with 4 decimal places.

For "total 2", we also round each product to 2 decimal places, then add. (The outer round is always prudent when dealing with non-integer arithmetic, in order to avoid binary arithmetic anomalies.)

There might even be a "total 3", since my differential multipliers differ from what we might do manually. TBD.

If you don't care about a small difference of 1 or 2 cents, I would suggest the formula for "total 1" in B10.

 

[joeu2004]

On second thought, forget about the formula for "total 2".

The "total 1" formula is correct. It always matches the result of the "total 3" formula below, which mimics how we would do the calculation manually, at least for units shipped with 2 decimal places up 800.00.

=ROUND($B$2 + ROUND(MAX(0, MIN(B9,$A$4)-$A$3)*$B$3,2)
 + ROUND(MAX(0, MIN(B9,$A$5)-$A$4)*$B$4,2) + ROUND(MAX(0,MIN(B9,$A$6)-$A$5)*$B$5,2)
 + ROUND(MAX(0,MIN(B9,$A$7)-$A$6)*$B$6,2) + ROUND(MAX(0,B9-$A$7)*$B$7,2),2)

Since "total 1" and "total 3" always match, I would definitely recommend the simpler "total 1" formula, even if it seems mysterious. It is a form that is commonly suggested for "progressive" calculations like yours (e.g. marginal tax rates, commissions).

 

[Joe4]

Can you post all the different levels, and price associated with each level?

Sorry, I couldn't see the image from the computer I was on earlier.
My workplace security settings not only block all file and image share sites, they also block some images.