Sep 01 2017 11:16 AM
I am producing a questionnaire that calculates a score. Answers to questions add different numbers of points to the score. Part of the questionnaire is Y/N answers, but others have a rating of 1, 2, or 3.
I was able to set up the questions with the score for a yes answer, and get an IF statement to produce the value, but cannot SUM the point subtotal/total.
Column C shows the points to add for a yes answer.
Column E is where you enter Y or N.
Column F uses an IF statement (example: =IF(E24=Yes,C24,0) )to show the points for a Y answer.
F29 is supposed to add the subtotal for column F. I am assuming the IF statement is producing the value as Text and that is why is it not adding.
What is the best way to do what I want?
Sep 05 2017 01:29 AM
SolutionCathe
Your C column shows a green triangle on the top left corner of each cell. This probably indicates the values in this column are stored as Text values (vs. Number).
Your IF formula is OK but as it takes the value from column C if the later is a Text value then in returns that Text value and logically SUM ignores it.
Several options: Convert the values in column C to Number values. If you can't change your formula as follow: =IF(E24="y",C24*1,0) =IF(E24="y",--C24,0). This will not change the value in column C but will convert it to a Number value in column F
Sep 05 2017 06:49 PM - edited Sep 05 2017 06:57 PM
Thank you! That was the answer. I changed Column C to numbers and that fixed it.
Apr 12 2022 10:20 AM
Apr 12 2022 01:20 PM
Your formula can be "simplified" as:
=IF(F13=1,15,IF(F13=2,12,IF(F13=3,9,IF(F13=4,6,IF(F13=5,3,IF(F13=6,0))))))
in which case you shouldn't need an adjacent column with =G13*1
If you run Excel > 2016 Or 365, 2 alternatives:
=IFS(F13=1,15, F13=2,12, F13=3,9, F13=4,6, F13=5,3, F13=6,0, TRUE,"Unknown")
=SWITCH(F13, 1,15, 2,12, 3,9, 4,6, 5,3, 6,0, "Unknown")
Apr 15 2022 10:04 AM
Sep 05 2017 01:29 AM
SolutionCathe
Your C column shows a green triangle on the top left corner of each cell. This probably indicates the values in this column are stored as Text values (vs. Number).
Your IF formula is OK but as it takes the value from column C if the later is a Text value then in returns that Text value and logically SUM ignores it.
Several options: Convert the values in column C to Number values. If you can't change your formula as follow: =IF(E24="y",C24*1,0) =IF(E24="y",--C24,0). This will not change the value in column C but will convert it to a Number value in column F