sum up the x in range to give proper percentage calculation

  • Hi all,


    I have a range in excel sheet where there are 5 possible choices to make. they all have a value in percentage. If the user will put an X in "never" it will have value of 0%, if they user puts X in "sometimes", it has a value of 6.25%
    There are 5 questions where the user can put a X depending on the answer she/he will give.
    these are the values I have added:
    Never = 0%
    Sometimes = 6.25%
    Often = 12.5%
    Mostly = 18.75%
    Always = 25%


    I a user would have chosen like this:
    (range N3) question 1= Never
    (range O3) question 2 = sometimes
    (range P3) question 3 =sometimes
    (range Q3) question 4 =often
    (range R3) question 5 =mostly


    I should get the total in the values 43.75%


    Any idea how to solve this would be really great.


    Greetings.

  • Re: sum up the x in range to give proper percentage calculation


    Assuming the questions are in N3:R3 and the answers "X's" are in the below 5 rows (each row represents different option) with options listed in M4:M8, then...


    create a separate list of the options with there respective values beside them... e.g. in V3:V7 list the options, Never, Sometimes, etc.. and in W3:W7 enter the percentages, 0, 6.25, etc.


    Now in N9 (bottom of Q1) enter formula:


    [COLOR="#0000FF"]=INDEX($W$3:$W$7,MATCH(INDEX($M$4:$M$8,MATCH("x",N$4:N$8,0)),$V$3:$V$7,0))[/COLOR]


    copied across. This row can be hidden if you don't want to see the individual scores.


    Enter a separate formula where you want the result: [COLOR="#0000FF"]=SUM(N9:R9)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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