• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Providing Score on the basis of deviation from average

Hi,

I am trying to provide score to each entry on the basis of deviation from the mean. In this case, Column A represents the actual margin, and column B represents the average margin of their respective industries. So, I am looking for a way to provide score on the basis of deviation.
For e.g. in one case avg is 15.2%
So, higher the positive deviation, higher will be the score, and lower the negative valuation, lower will be the score.
 

Attachments

  • Score.xlsx
    24 KB · Views: 5
You'd need to give bit more detail on how you'd calculate score... is it out of max 100, 200 or some other value?

Or if you need simple rank. I'd add helper column for calculating variance. Then use Rank.EQ() function.

While it's probably possible to do it without helper column. It creates less overhead in calculation and has better workbook performance.

And makes it much easier for others to understand the calculation steps.

Edit: If you want to give higher score for high positive variance. Just add optional argument for Rank.EQ() function to rank Ascending.
 

Attachments

  • Score.xlsx
    53.9 KB · Views: 3
You'd need to give bit more detail on how you'd calculate score... is it out of max 100, 200 or some other value?

Or if you need simple rank. I'd add helper column for calculating variance. Then use Rank.EQ() function.

While it's probably possible to do it without helper column. It creates less overhead in calculation and has better workbook performance.

And makes it much easier for others to understand the calculation steps.

Edit: If you want to give higher score for high positive variance. Just add optional argument for Rank.EQ() function to rank Ascending.

Hi Chihiro,

It looks fine, but i was looking to provide a maximum score of 100 on the basis of deviation.
 
Then you'd do base index calculation based on rank.

First in some cell (I used D1) calculate Max of rank column.
=MAX(D5:D880)

Then in column E.
=100/$D$1*D5

copy down.

Edit: Forgot to change to Ascending rank. Change made and re-uploaded file.
 

Attachments

  • Score (1) (1).xlsx
    67.1 KB · Views: 2
Hi ,

It would help if you could have a column mentioning the industry ; at present , going by the averages in column B , there are 5 different values of 13.8 , 14.1 , 15.2 , 15.7 and 22.2 ; do these 5 different values represent 5 different industry sectors or do they represent more than 5 industry sectors ?

The margins for an industry average of 15.2 range from -1.8 to 67.2 ; it is difficult to imagine that one industry sector can have such a wide variation.

If indeed it is one sector , can we index the margins , so that 67.2 scores 100 ? Accordingly , all other margins can be scored.

Narayan
 
To add to what Naryank wrote.

It's probably better to categorize/group based on variance rather than giving score. I.E. Histogram chart.
 
Back
Top