Calculating Volume from Percentage of Target

[Workguy101]

Hi

Hoping someone has the answer to the following.

I have a production volume target figure for each month that is charged at a fixed rate. The same rate will be charged for all volume with 5% of the target. If the volume exceeds 5%, the exceeded amount will be charged at a different rate, whilst the volume within 5% will be charged the original rate.

Example: Target volume is 100,000, target cost at 10p = £10,000. If the actual volume is 110,000, volume and cost will be 105,000 at 10p =£10,500 and 5,000 at 12p = £600.

I need formula(s) that compares the actual against the target and shows in first result column the actual volume within 5% of the target, in the second result column shows the actual volume within 10% of the target without including the volume already shown in the first result column. It should look something like below when it works. I can add separate columns to show the cost.

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]>95% & =<105%[/TD]
[TD]>105% & =<110%[/TD]
[TD]>110% & =<115%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]100,000[/TD]
[TD]112,000[/TD]
[TD]105,000[/TD]
[TD]5,000[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]100,000[/TD]
[TD]103,000[/TD]
[TD]103,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance

WG

 

[Special-K99]

Does this work? Play around with some figures to make sure.

With "Month" in A1 (adjust the formula required as you haven't mentioned any cell references).

in D2
=IF(C2>=B2*95%,IF(C2>=B2*105%,B2*105%,C2))

in E2
=IF(C2>=B2*105%,IF(C2>=B2*110%,B2*5%,C2))

in F2
=C2-(D9+E9)

This is very similar to "tiered commisions" which is a common Excel requirement so if this fails you may be able to Google Tiered Commissions which may give you more help.