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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
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,119,099
Messages
5,576,128
Members
412,698
Latest member
Lilly13m
Top