Excel-Weighted Average using Words, NOT Numbers!

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

  • Attached is the form I created. The fields are marked with Y under yes and N under no. N/A has no bearing on the score and the automatic fail columns also bare no weight right now. I'm not sure if this is something that can be done or not. Which is why I turned here for help. Thanks!

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