Excel-Weighted Average using Words, NOT Numbers!

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello!
    I have created a form and the average is computed based off of "Yes's" and "No's" rather than numbers. This is in two columns and they're averaged in a separate cell. I need to put weighted numbers on the yes's and no's due to their importance. Is this possible using a simple letter? If so, can someone guide me? I appreciate any help!

    Possibly. Explain the weighting system you have in mind.

    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

    Good Morning! I'm not too familiar with it but something simple (at least to me lol) would be grading them on a 1-5 scale with 5 being most important in weight.

    How could 'yes' and 'no' answers be graded on a 5-point scale???


    Please attach a workbook - manually mock up what you are hoping to achieve.

    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

    Let me try to explain what it is. It's a call scoring form for a Call Center. Items that are marked as Y and N if they were completed correctly. I have a cell that averages the Y's and N's to compute a %score but I need some of the items to have more weight than others. I will attach a workbook.

    I am trying to open your file now and have a look.


    EDIT: OK, so how do you want to weight the various sections?

    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

    Are you happy to use a hidden helper column?

    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

    I'm not sure what that is, but if it's hidden and people can't make changes, yes, that would work for me. I tried created a hidden column for the weighted numbers but couldn't get it to work correctly.

    Have a look at L3 - is this the sort of thing you are after? Bear in mind that column A can be hidden.

    The helper column can be hidden and then the sheet protected to stop people from accessing it.

    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

    Yes! For the columns. I notice you put Y and N in the same, which is great. When I get to the Automatic Fail and type Y, it doesn't change. Can that weight be really high to somehow bring the score a lot lower (basically that's a huge fail). And in the future, if the weights need to be adjusted, is that something I can just type over?

    Yes, all of this can be done, however I have to go offline now. I will look in again tomorrow morning and help you further if nobody else has stepped in. We are making good progress! :)

    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

    Such Progress! I did notice that I don't have the automatic fail calculated in my average, probably the problem. I look forward to the further help and you, my friend, are ROCKSTAR!!! Thank you a ton for getting me this far! :)

    Good morning!


    I have a question for you:


    Do you want the percentage reflected at the top of the form to be based on just sections that have a 'Y' or 'N' response, or on all sections? Obviously the percentage will be different if you are only focusing on sections where there is an answer.


    The automatic fail section is a problem: it's basically a negative section, where all other sections are positive, so it needcs to be handled differently. I shall have a think about it and see if I can come up with something for you that will work - how many of the automatic fails does a person have to have to fail overall? Is it just one of them or all three?

    Good Morning and Happy New Year!


    First, let me say thank you for all of your help so far. I greatly appreciate it!


    On the form, I would say that I only need the columns that have Y/N in them, since the N/A column means it doesn't apply to that phone call. That way, if it's not factored, it doesn't affect the score (right?). On the automatic fail, they just 1 of them marked and that would cause it to be a failing call. So, if any one of those 3 things happen, they automatically fail. I did play with the numbers the other day and when I changed the weight on those failing items, I really increased it, and it would compute an overall score of 1% but couldn't get it to go any lower than that (that was with all "yes's" and one autofail).

    Happy New Year to you, too!


    I would recommend that you deal with those three fail items separately. I'll have another look at the workbook and post a suggestion in a while.

    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

    OK, so in the attached, for the main set of Qs and As, only those with a 'Y' or 'N' response are counted in the percentages. The three 'fail' questions are assessed separately, but the monitoring box at the very top shows 'FAIL' if any of those has been marked as 'Y'. Let me know if this is now closer to what you want.

    Yes! I seems to work exactly how I want it to. I will begin using it immediately and let you know if any other questions pop up! I do have another question. If I want to change the weight on items, can I simply update the number in column A and it'll take without having to do anything else?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login