Not sure what you are trying to do exactly. However, my best guess is you would like to score critical as say 4, Major as 3 and so on. Then add all the scores up at the end of the 111 questions.

Again, not sure why you would want to do this. However, If in column a1 you have any of the listed question outcomes ie Critical would score 4 in descending order of score value. Then you could try a nested if statement.

=IF(A1="critical",4,IF(A1="Major",3,IF(A1="minor",2,IF(A1="OFI",1,IF(A1="Conforms",0,0)))))

This assumes the answer is in cell a4 (obviously when you copy it down it will auto change to a2, a3 etc. You just need to change this to be whatever cell your answer is in. I have scored both Conforms and anything not the others as zero. You have to ensure spelling is exact each time, not good as “criticl” would score 0. Then just sum that column at the bottom

In the above example formula critical=4, Major=3, Minor=2, OFI=1 and Conforms or N/A or Banana would score 0

Why not do it the other way ie type 4 and it puts “critical” in the box next to it.

Have fun, sometimes you need to try things to see its not a good idea. But hay

Chris

It can't really be percentages, becasue if you're saying a critical is 5%, and you have 111 questions tehre's potential to score -455%, which makes no sense.

So it's just a score, and chris42's equation will owrk as well as any, it's just that instead of scoring 4, 3, 2, 1, 0, you'll be scopring -5, something, -1, something, 0.

Actually, I prefer one of two differnet approaches to chris42's: Mostly I'd rather have a separate scopre table that lists teh answers and teh possible score, then use a vlookup to get teh score.  Thsi makes it trivial to regrade (e.g. if you decide a mnor should be -2 instead of -1, or a critical tshoudl be -20) - you just change teh value in teh lookup table.

If you don't want to use a lookup, rather than a long nested chain of 'if' statements, I find it tidier to use e.g.

=CHOOSE(MATCH(A1,{"conforms","OFI","minor","major","critical"},0),0,-1,-2,-5,-10)

So here, the 'MATCH(A1,{"conforms","OFI","minor","major","critical"},0)' looks up what is in A1 in the list (between {} marks) and returns 1 if it's the first, 2 if it's the second, 3 if it's the third, etc.  (Match is not case-sensitive btw so 'Conforms' and 'conforms' and even coNfOrMS wills core correctly.)

Then the CHOOSE(??,0,-1,-2,-5,-10) returns the first value (0) if ?? is 1, the second value (-1) if ?? is 2, the third value (-2) if ?? is 3, etc.

So together it returns 0 for 'Conforms', -1 for 'OFI', -2 for 'Minor'. Sum them, add them to 100.  Or set the scores as positive values and subtract the sum from 100.

You might want to use a validation and possibly a drop-down for the input cells so only recognised values can be entered in the cells - look at data tab on the ribbon and 'data validation'.  This depends how consistent your typing (and that of the people using the sheet) is.

Edited by user 15 December 2020 15:30:00(UTC)  | Reason: fix percentage (doh)

To be honest I would probably use lookup tables also, but I thought it easier for someone to get their head around a good old “if statement”. I could be wrong on that though. Tweaking at a later date wouldn’t be much of an issue you only need to the first one then copy and past down the 111 questions, a little more work only.

To get rid of the negativity, score the way I suggested ( ie think of it as taking points off. So, if you want to go to 5 for critical then the max bad score would be 555 (max good score is zero). If you then score 333 when added up, you take this away from 555, so you get 222 which you then calc as a percentage of the max so you end up with (222/555) x 100 for you score. In this case that would be 40%. If everything is perfect then you add up all the zeros to get zero take this from 555 and you get 555 and so 555/555 x 100 =100%

This is far more interesting than writing up audit reports as I’m supposed to be doing.

Chris

Where column D is where all the scores are from d1 to d111 (you can adjust if different)

=((555-SUM(D1:D111))/555)*100

My audits are in Word which is scored just to note overall improvement and I just add it up manually.  Well I add up what I will take off, so like you they start with 100% and go down hill from there on :0)  I inherited it from my predecessor, but it seems to work for them.

Things have improved significantly since I started so I may change it completely next year.

You now have three different ways of getting the score (1 from me and 2 from Achrn) in column D and the above should work to do the score at the bottom.

Chris