Contribution to an overall % growth (Math Question)

[onthegreen03]

Hi all, I have a group of countries with their YTD forecasted volume and YTD actual volume, with a % variance. Simple enough. I then have a subtotal of all those countries, with a calculated YTD forecasted and actuals volumes and the overall variance. Also simple enough. Because each country has different volume levels I am trying to figure out a way to calculate the contribution from each country to the overall variance. See the example below. The overall variance is -7%, I want to calculate the weighted average variance by country (blue boxes) that would add up to the -7%. I know there is a way to do this I just can't remember the exact formula. Hope this makes sense and thanks for the help.

 

[Eric W]

Try:

Book2
	A	B	C	D
1		YTD
2	FC	74373
3	ACT	68725
4	VAR	-8%	Country1	-3.66%
5
6	FC	30349
7	ACT	29069
8	VAR	-4%	Country2	-0.83%
9
10	FC	10312
11	ACT	7346
12	VAR	-29%	Country3	-1.92%
13
14	FC	21408
15	ACT	19267
16	VAR	-10%	Country4	-1.39%
17
18	FC	18032
19	ACT	19008
20	VAR	5%	Country5	0.63%
21
22	FC	154474
23	ACT	143415
24	VAR	-7.16%	TOTAL	-7.16%
25
Sheet4

Cell Formulas
Range	Formula
B4,B24,B20,B16,B12,B8	B4	=(B3-B2)/B2
D4,D20,D16,D12,D8	D4	=(B3-B2)/B$22
B22	B22	=SUMIFS(B$2:B$20,A$2:A$20,"FC")
B23	B23	=SUMIFS(B$2:B$20,A$2:A$20,"ACT")
D24	D24	=SUM(D4:D20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4,B8,B12,B16,B20,B24	Cell Value	<0	text	NO
B4,B8,B12,B16,B20,B24	Cell Value	>0	text	NO

 

[onthegreen03]

Thanks, very nice.