# Formula for calculating contribution to change/result

##### New Member
Hi,

I hope I manage to explain this in a way that makes sence.

I have two departments which are rated by customers every week. Calculating the change in average score for each department is simple enough, but I need a formula that calculates hos much each department has contributed to the increase and/or decrease.
So the formula needs to account for all eventualities, no change in either departement and no total change, positive in one department, negative in the other and positive change total, positive in one department, negative in the other and negative change total, positive in one department, positive in the other and positive change total, and so on and so on...

I have tried writing av formula that has both IF and AND-functions, but eventually I encounter an error. So I'm thinking I need to put in OR-function(s) aswell.

Is it possible to get this entire formula in one cell?

Any ideas, anyone?

Few examples below.

Ex. 1.
Dep. A has 100 responses in week one, with a total of 500 points, the avg. score then is 5. If it in week two has 200 responses and 1000 points, the average is the same.
Dep. B has the exact same result.
Total change = 0
There is no increase in the result from one week to the other, but with no change both departments should be calculated as contributing 50% each to the result.

Ex. 2.
Dep. A has 100 responses in week one, with a total of 500 points, the avg. score then is 5. If it in week two has 200 responses and 1100 points, the average is now 5,5.
Dep. B has 100 responses in week one, with a total of 500 points, the avg. score then is 5. If it in week two has 200 responses and 1000 points, the average is 5.
Total change = 0,25
Dep. A has contributed 100% to the total increase.

Ex. 3
Dep. A has 100 responses in week one, with a total of 500 points, the avg. score then is 5. If it in week two has 200 responses and 1000 points, the average is 5.
Dep. B has 100 responses in week one, with a total of 500 points, the avg. score then is 5. If it in week two has 200 responses and 950points, the average is 4,75.
Total change = -0,13
Dep. B has contributed 100% to the total decrease.

And so on...

Regards

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Snakehips

##### Well-known Member
@MadBern I cannot be sure whether or not the below covers all scenarios in the way you intend but if not, then maybe it's food for thought.
The formulas will likely vary according to your data layout.
Book1
ABCDEFGHIJK
1Dept1Dept 2CombinedChangeContribution
2RespPointsAverageRespPointsAverageAverageDept 1Dept 2
3Week
41100500510050055550.00%50.00%
52100500510050055050.00%50.00%
6320011005.510050055.250.25100.00%0.00%
74100500510050055-0.25100.00%0.00%
8520011005.5200100055.250.25100.00%0.00%
96100500510050055-0.25100.00%0.00%
107200100052009504.754.875-0.1250.00%100.00%
1181005005100500550.1250.00%100.00%
1292009504.75200100054.875-0.125100.00%0.00%
1310200100052009504.754.87500.00%100.00%
141120012006200100055.50.62580.00%20.00%
151210060061001000101610.50.00%100.00%
1613100300310050058-837.50%62.50%
Sheet1
Cell Formulas
RangeFormula
G4:G16, D4:D16G4=IFERROR(F4/E4,"")
H4:H14H4=IFERROR((G4+D4)/2,"")
I4:I16I4=IFERROR(H4-H3,"")
J4:J16J4=IFERROR(IF((D4-D3)=(G4-G3),0.5,IF(SIGN(D4-D3)<>SIGN((D4-D3)+(G4-G3)),0,(D4-D3)/((D4-D3)+(G4-G3)))),"")
K4:K16K4=IFERROR(1-J20,"")
H15:H16H15=IFERROR(G15+D15,"")

##### New Member
@MadBern I cannot be sure whether or not the below covers all scenarios in the way you intend but if not, then maybe it's food for thought.
The formulas will likely vary according to your data layout.
Book1
ABCDEFGHIJK
1Dept1Dept 2CombinedChangeContribution
2RespPointsAverageRespPointsAverageAverageDept 1Dept 2
3Week
41100500510050055550.00%50.00%
52100500510050055050.00%50.00%
6320011005.510050055.250.25100.00%0.00%
74100500510050055-0.25100.00%0.00%
8520011005.5200100055.250.25100.00%0.00%
96100500510050055-0.25100.00%0.00%
107200100052009504.754.875-0.1250.00%100.00%
1181005005100500550.1250.00%100.00%
1292009504.75200100054.875-0.125100.00%0.00%
1310200100052009504.754.87500.00%100.00%
141120012006200100055.50.62580.00%20.00%
151210060061001000101610.50.00%100.00%
1613100300310050058-837.50%62.50%
Sheet1
Cell Formulas
RangeFormula
G4:G16, D4:D16G4=IFERROR(F4/E4,"")
H4:H14H4=IFERROR((G4+D4)/2,"")
I4:I16I4=IFERROR(H4-H3,"")
J4:J16J4=IFERROR(IF((D4-D3)=(G4-G3),0.5,IF(SIGN(D4-D3)<>SIGN((D4-D3)+(G4-G3)),0,(D4-D3)/((D4-D3)+(G4-G3)))),"")
K4:K16K4=IFERROR(1-J20,"")
H15:H16H15=IFERROR(G15+D15,"")
Brilliant!
Needed a few tweaks, but worked more or less perfectly.

Thanks a bunch!

Regards

Replies
4
Views
205
Replies
21
Views
277
Replies
0
Views
69
Replies
1
Views
115
Replies
4
Views
127

1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back