Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Distributing costs based on population size

0

A number of councils have decided to come togther to fund joint projects, however it has been decided to distribute the cost based on the population size of the Council area. The total budget is £120,000 and there are 8 councils. What excel formula can I use to do this?

Answer
Discuss

Answers

0

Hi Cats and welcome to the Forum.

This feels like a schoolwork question (which we're not here to do) but the answer is pretty easy.

In the attached file, the imaginary populations of 8 councils appear in cells B4:B11 (for you to change). Cell B12 gives the combined population as

=SUM(B4:B11)

Given the combined budget is in B1 (for you to change), each council has a budget which is that multiplied by the ratio of its population to the total population of all councils, so in C4 the formula is:

=$B$1*B4/$B$12

where the $ signs fix the rows and columns for B2 and B12 (so they don't change when the formula is copied down to give each council's apportioned budget).

C12 just checks they add up, using:

=SUM(C4:C11)

You might need to use the ROUND function is real life.

Hope this helps. Please mark this Answer as Selected if so.

Discuss

Discussion

Did that solve your problem, Cats? 
John_Ru (rep: 3992) Sep 21, '22 at 12:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login