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?
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.)
Distributing costs based on population size
0
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$12where 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.
Discussion
Did that solve your problem, Cats? No comment?
John_Ru (rep: 6142) Sep 21, '22 at 12:17 am