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.

Item Name	# of Items	Tier	Rate per item	Budget
Item1	30	1
Item2	23	2
Item3	10	1
Item4	9	3
Item5	14	1
Item6	12	3
Item7	20	2
Item8	8	3
Total	126		79.36508	10,000

<tbody>
</tbody>

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!