Multiple calculation problem

[tartan tim]

I am secretary to a society. We have 10,580 members. There are 4 categories
of membership. Each category of member pays a different annual fee to cover
annual expenses. Annual expenses are £2,500,000. There is a base fee and this
is calculated by dividing member numbers into budget so this is £2,500,000
divided by 10,580 = £236. 500 members pay the base fee.

The other categories pay the balance of the expenses. One group pay a top
fee - there are 7000 in this group. Another group pays one half of the top
fee there are 1500 in this group. The last group with 1,580 members pay one
third of the top fee.

How do I calculate what the top fee should be? Bear in mind that annual
expenses can change and membership numbers in each category can vary year to
year.

 

[Bob I]

"topfee"= (Balance+(annual expenses-(500*236))) /(7000 + (1500/2) +
(1580/3))

 

[tartan tim]

Thanks Bob
I think you are on the right track but I am getting a "0" return on this
formula. Is this VBA?

 

[Bob I]

No it's actually a verbal description of what you want to do. If you sit
down with a piece of paper you will work it out, then simply transfer it
to the excel sheet.

 

[Fred Smith]

Tartan,

Bob doesn't know where you are storing your data because you didn't tell
him. So he can only generalize. You have to take his formula and substitute
proper cells.

Suppose your Balance is in A2, and your Annual Expenses are in A3. Then you
would have:

=(a2+(a3-(500*236))) /(7000 + (1500/2) + (1580/3))

Modify those addresses to suit your situation.

Regards,
Fred

 

[Herbert Seidenberg]

No paperwork required.
No editing of formulas when
expenses, membership changes.
http://www.freefilehosting.net/download/3gb8e