Determine values with mix change

[DaveWagner_]

I have (2) dimensions with values in (2) years, which sum to a total value for each year. The mix of those values from year 1 to year 2 changes. I want to input a hypothetical value total and the formula to return the "should be" mix for each of the (2) dimensions.

Example:

Year 1
Consulting is 14,000 (30% of total)
Distribution is 33,000 (70% of total)
Total is 47,000

Year 2
Consulting is 15,710 (18% of total)
Distribution is 69,290 (82% of total)
Total is 85,000

Hypothetical
Consulting is XX,XXX (XX% of total)
Distribution is XX,XXX (XX% of total)
Total is 72,500 (I enter this value and the formulas provide the XX results above)

 

[DaveWagner_]

Would this work for you?

Book1.xlsx
	A	B	C	D
1		Year 1	Year 2	Hypothetical
2	Consulting	14,000	15,710	15147.5
3	Distribution	33,000	69,290	57352.5
4	Total	47,000	85,000	72,500
5
6	Consulting	29.79%	18.48%	20.89%
7	Distribution	70.21%	81.52%	79.11%
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=(C2-B2)/($C$4-$B$4)*($D$4-$B$4)+B2
B4	B4	=SUM(B2:B3)
B6:D6	B6	=B2/B4
B7:D7	B7	=B3/B4

View attachment 116004

View attachment 116005

It does. Same end results as my methodology, but with a different approach. Very nice! Thank you!

 

[felixstraube]

You are welcome. Thanks for the feedback!