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

Create a formula to compare different metrics

farrusete

farrusete

Member
Hi,

Maybe this is more a math question than an excel one so sorry in advance if this is an offtopic but id appreciate if someone could help me on this.

I have several tests delivered to a group of users and id like to have a column to have some kind of "success rate" related to the test itself to compare each test with the others. Maybe a number between 0 (it was a fail) to 1 (it was a perfect test) not just to take into account the test results but also the rest of metrics (data infered from the test) which are splitted in below columns (one per each test):
  • Days since the test was published
  • Total questions amount in the test
  • Average (%) of answered questions (no matter if the answer is ok or wrong but just if the user abandoned the test)
  • Total amount of users who start the test (those arriving to the first question)
  • Total amount of users who end the test (those arriving to the last question)
  • Test score (just 4 possible values: 2 is very good, 1 is good, 0, -1 is bad and -2 is terrible). This is not the score from correct answers but a manually selected score by an external consultant.
  • Test metric: This is the value im looking for which gives a number depending on the rest of fields.
Thank you in advance,
 

Attachments

  • tests.xlsx
    10 KB · Views: 7
Interesting. There are probably several ways to do this. One important consideration is units. First the scale of the 'comparing' metric would need to be selected e.g. 1 to 10 or 1 to 100 (just as an example). Then all the the other metrics would need to be converted into this same scale e.g. for column D, 90% = 9 or 90 depending on the scale examples above. Then an average or weighted average or even a simple sum could be calculated to estimate the comparing metric. Not sure if this was what you were looking for?
 
I think you need to step back and ask yourself, what you / your organisation wants to get from the results?
Because that will direct you going forward

You should not be just multiplying all the measurements together whether actual or normalised, as I doubt some of the measurements have any correlation to each other

You may want to also consider charting the data and looking for correlations
 
You must log in or register to reply here.
Back
Top