calculating formula

[kare13]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a data validation list with text values, each text value theoretically has a numerical value. I need to calculate the theoretical value when a corresponding text value is chosen.<o></o>
Can anyone help?<o></o>
I.e. the text value is sometimes and carries a numerical value of 1. If I have several questions that are answered with the sometimes answer I need to calculate the total of all the answers. Thanks much<o></o>

 

[jproffer]

Ten questions worth:

In B1:

=IF(A1="never",0,IF(A1="sometimes",1,IF(A1="always",2,"ENTRY ERROR")))

drag down to B10

in B11 (or wherever you would like your sum to be)

=SUM(B1:B10)

Questions are in column A.

EDIT: Also change the 0 and the 2 in the formula to whatever you want the value to be for the "other than sometimes" entries.

 

[kare13]

THANK YOU - if I could I would hug you. You saved my day.

 

[jproffer]

I'm just glad I was even somewhat CLOSE to what you wanted. I was guessing on the "always/never" parts...and on the values, but I see you got the general idea.

Glad it worked.