# 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!