Calculating effective rate for fixed budget

[Joven99]

Hi, this may be a very basic question but hopefully that means there's an easy answer!

I have a fixed budget (let's call it £10,000) and I have a set number of items that I have to sell. Different items are valued at different levels and I need to calculate a budget per item, using a consistent rate based on the tier of the item.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item Name[/TD]
[TD]# of Items[/TD]
[TD]Tier[/TD]
[TD]Rate per item[/TD]
[TD]Budget[/TD]
[/TR]
[TR]
[TD]Item1 [/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD]23[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item4[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item5[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item6[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item7[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item8[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]126[/TD]
[TD][/TD]
[TD]79.36508
[/TD]
[TD]10,000[/TD]
[/TR]
</tbody>[/TABLE]



I want the rate per item to reflect the tier, so if it's a tier 3 item I want it to be valued at an effective rate of 3 times the rate of a tier 1. Do you know how I can calculate the right budgets for each individual line based on the info above? Do I need more info?

Thanks,

Joven

 

[footoo]

In E2 (Budget) enter : =ROUND(E$10/SUMPRODUCT(B$2:B$9,C$2:C$9)*B2*C2,2)
Fill down to E9.

In D2 enter : =ROUND(E2/B2,2)
Fill down to D9.

 

[Joven99]

That's perfect, thank you very much footoo!