I'm doing some data analysis where I have a group of stats for people. For example, there's a column on sales per month.
I want to be able to look at the sales numbers in various ways but one thing I want is to look at all the numbers in a column (sales per month) and get the average sales. So, I have a cell at the bottom that's the average of that column. Simple enough.
Then what I want is a column next to that column that then shows me a "grading" based on that average.
Let's so the average is 70 sales a month. Then let's compare two rows (people).
Joe has 80 sales and Bob has 60 sales. What I want to be able to show in the new column is not just a difference (ie, Joe +10, Bob -10). Instead what I want is to take the average and, IF the person's number is above the average, give them a rating of .10 per point above the average number but if it's below the average then give them a rating of -1.0 for every point below average.
Thus, in the example above, Joe would get a 1.0 and Bob would show as -10. This works out in the end as there are dozens of stats to look at and while Bob may be bad in this one he's good in others. The point of it is that if you're doing better than expected that's great but only marginally important over those who aren't even hitting the expected numbers.
This is just an example so no need to argue about the value of positive sales over negative. I just need to know how to pull this off in an excel formula.
I want to be able to look at the sales numbers in various ways but one thing I want is to look at all the numbers in a column (sales per month) and get the average sales. So, I have a cell at the bottom that's the average of that column. Simple enough.
Then what I want is a column next to that column that then shows me a "grading" based on that average.
Let's so the average is 70 sales a month. Then let's compare two rows (people).
Joe has 80 sales and Bob has 60 sales. What I want to be able to show in the new column is not just a difference (ie, Joe +10, Bob -10). Instead what I want is to take the average and, IF the person's number is above the average, give them a rating of .10 per point above the average number but if it's below the average then give them a rating of -1.0 for every point below average.
Thus, in the example above, Joe would get a 1.0 and Bob would show as -10. This works out in the end as there are dozens of stats to look at and while Bob may be bad in this one he's good in others. The point of it is that if you're doing better than expected that's great but only marginally important over those who aren't even hitting the expected numbers.
This is just an example so no need to argue about the value of positive sales over negative. I just need to know how to pull this off in an excel formula.
