Formula for calculating contribution to change/result

MadBern

New Member
Joined
May 10, 2019
Messages
35
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
Mads
 

Some videos you may like

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
Joined
May 17, 2009
Messages
5,122
Office Version
  1. 2013
Platform
  1. Windows
@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,"")
 

MadBern

New Member
Joined
May 10, 2019
Messages
35
@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
Mads
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top