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
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
