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.

Month	Target	Actual	>95% & =<105%	>105% & =<110%	>110% & =<115%
January	100,000	112,000	105,000	5,000	2,000
February	100,000	103,000	103,000	0	0

<tbody>
</tbody>

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.