Excel-Weighted Average using Words, NOT Numbers!

  • Yes, that is correct, and you can hide that column when you don'’’’t need to see it. I am glad it's what you wanted. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Ali, I used it and think I gave incorrect information. :( I have been playing with it but have had no luck. It does need the N/A column. Is that possible? If it's not, I can just mark yes, whether it applies or not but I completely didn't know if would affect the score (opportunity for higher score) without being in there. Anyway you could help me again? (I feel so bad for asking again being I didn't understand how it would work.)

  • Why is it not working? At the moment, the calculation does not take any of the unanswered questions as far as B38 into account if they have no answer, so calculates the score based on only those answered, which is what you said you wanted in post #17. Please give me an example (manually mocked up) of the results you are wanting/expecting, explaining how the N/A column will influence these. Attach your manually mocked up spreadsheet here. How can N/A affect the opportunity for a higher score? What you are saying doesn't make much sense to me, I am afraid. Is it simply that you want the calculation out of a total possible score rather than a sum of the score possible for the answers given? If so, that's easy to fix.


    Is the attached what you want?

  • Good Morning!
    I attached a mockup and it has 3 separate sheets to compare so you can get an idea of what I'm running into. What's happening is if 1 rep. only has 5 items that apply to a phone call, they get more hits when a negative item is marked. Then say another rep. has all items that apply to their phone call, their No's are counted a lesser points removed, leaving it not equal between people and scenarios. Sheet 1 and 2 show the ratio difference I'm talking about (was completely my fault because I didn't realize it would work out this way). Sheet 3 shows how I need it to work, which is basically, the N/A column to also be a positive, IF it doesn't apply. I appreciate your patience and help. I'm not the best at Excel and how it all works out in the end.
    If a calculation of possible score renders everyone with the same opportunity, no matter how many Yes's apply, then yes, that would be what's needed.

  • This goes beyind the scope what we offer here on the forums, I am afraid. I can help you with the mechanics of applying your requirements, but not with the maths that is going to make your weighting system work - sorry. That is something you need to decide upon and make sure is going to work first. When you have done that, by all means come back if you need help turning that into an auotmated form.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • It's been a pleasure. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules