Changing expenses to get target percentage 100%

[halb]

I have three columns, revenue, expenses and percentage. Percentage is just the individual expense divided by revenue. Is there any way to change the expenses column numbers so that the total percentage at the bottom which is currently 19%, will change to 100%, so I want the numbers in expenses column to change so that the total percentage will become 100%. Is there a way to do this by formula or what if analysis.

	Revenue	Expenses	Percentage
	3051​	109​	4%​
	1607​	606​	38%​
	4910​	111​	2%​
	3628​	636​	18%​
	1630​	884​	54%​
	1009​	444​	44%​
	3514​	104​	3%​
	1354​	869​	64%​
	1912​	869​	45%​
	2733​	864​	32%​
	3861​	750​	19%​
	3421​	732​	21%​
	1613​	460​	29%​
	3920​	291​	7%​
	2423​	596​	25%​
	4262​	783​	18%​
	4473​	331​	7%​
Total	49321​	9439​	19%​

 

[Shawnhet]

I'm not sure exactly what you're asking here but the Total Expenses are ~19% of the Revenue so if you changed the formula in the percentage column to C2/B2/.191379 that should give a 100% in the Total row for the Percentage column.

Not sure what this is supposed to mean though.

 

[halb]

I want to know how to change the individual amounts in expenses column so that the total percentage will change to 100%. The total percentage amount is total expenses divided total revenue

 

[Shawnhet]

Well. you could do this any number of ways, you need to increase the total expenses by ~$40K to get to 100%, you could put that full increase in one cell if you wanted.

I think you have a pattern in mind but you need to be more explicit in what exactly you are trying to get.

 

[halb]

I want to know if there is a way to do it by using solver or what if analysis, because I want there to be logic behind, I don't want the numbers to change randomly

 

[Shawnhet]

What are you trying to do exactly? You could probably get there with Solver.

 

[halb]

So I want the individual numbers in the expenses column to change, so that the total percentage which is determined by the formula of total expenses divided by total revenue, will change to 100%. Currently the total percentage is 19% but I want to see by using solver or maybe a formula, if I can determine logically how to change the individual amounts in the expenses column so that it will change the 19% to 100%

 

[Shawnhet]

Well, Solver could definitely give you some numbers here but I am not sure what use this will be to you.

Here's a good basic discussion of Solver:

Define and solve a problem by using Solver - Microsoft Support

How to use Solver in Excel to determine the maximum or minimum value of one cell by changing other cells.

support.microsoft.com