I am trying to assign a value to letters (Y,N). In some columns I need Y to equal "10 and others I need it to equal "3". Same with N. When all "Y's" and "N's" are entered I need to add them up to get a score. Any help would be helpful as I am a rookie when it comes to this sort of thing so if you could break it down "big Bird" style for me it would help a bunch. Thanks.

Just to clarify, I have a "yes or no" answer sheet with about 15 questions. I would like the "Y" in question 1 to have a greater value than a "Y" in say question 7. The same goes with the "N" answers. Then I would like to add up all the totals to get a final number such as 56.

I may need a little more clarification.

Let's take question 1 where Y = 10. Does N also have a value for that question?

If so, you could use a nested IF statement for each question as follows:

Assume your first question is in A1 and Y (worth 10) or N (worth 5) will be placed in B1. In C1 enter:

=IF(B1="Y",10,IF(B1="N",5,""))

For the question in A2, you might have:

=IF(B2="Y",7,IF(B2="N",3,""))

In other words, change the values for Y and N in the formula for each question. Once you have all the values in column C simply sum the values.

A more elegant way to do this would be to use a VLOOKUP table. You could put the same formula in every cell in column C which would lookup the value for Y or N based on values you put in a table stored elsewhere in the sheet. If you ever wanted to change the point vlaue for a Y or N for a given question, you would simply change the value in the table and not have to alter any formulae.

An example of the formula you might use follows. I can explain it in more detail if you are interested in this method.

=IF(B1="Y",VLOOKUP(ROW(),$G$1:$I$12,2,1),IF(B1="N",VLOOKUP(ROW(),$G$1:$IH$12,3,1),""))

You will want to use either an IF statement or a nested IF formula.

For example: IF(A2="Y",9,1)
Meaning If A2 shows a "Y" then enter the value to be "9" else "1".
So if yes is the answer then the score will be 9 and if No then the score will be 1.

Can you provide specific values you are looking for? Explaining nested if statements is too much to ask without specifics.

Bryan

I was sitting on that for a while but I see we both require additional information.

Bryan

The form I am trying to make will be something like:

Y N N/A

Are there signs?
Is the house locked?
Does it have a pool?
Is the pool secured?
Has the grass been mowed?

And so on, They will either put an "X" "Y" or "N" in the column for each question ( I really don't care if I use "X's" or " Y's and N's") But the "N" answer for whether or not the pool is secured needs to have more value ( or more weight) than a "N" for whether the grass was mowed since it is a safety hazard. Once it is all done I want to get a total score of 1-100 with 100 being a perfect hose and 0 being one that is completly messed up. Hope this helps.

re: IF(A2="Y",9,1)

We should also point out that without using a nested IF, the OP will get a 9 for a Y and a 1 for anything else, including a blank.

This is just a clarification for the OP, not a correction of your answer. You went as simple as possible, and I understand why. Perhaps by reading both posts, the difference will be evident to the OP.

In my last post the Y N and N/A were supposed to be slid over to the right. So everything in column B will be a Y and everything in C will be a N and D will be N/A.

But I need the "Y" in B3 to have more weight than a "Y" in B4. So basically I need to be able to assign a different value to each answer in every row.

I think that both the nested IF and VLOOKUP solution will work, but you may want to rethink your value system.

You say a 0 is a messed up house, but with values for both Y and N, you'll never get a zero.

What did I miss?

Based on your latest post, you could still use a nested IF. Please take a look at the IF() function in Excel Help so you'll understand how it works.

For any given question, let's assume:

The question is in column A and some type of character will go in either column B, C or D (but only one of those columns) to denote the answer as Yes, No or N/A.

For question 1, placed in A2, I'll assume a Yes (B2) is worth 10, a No (C2) is worth 5 and an N/A (D2) is worth 2.

In E2 put this formula:

=IF(B2<>"",10,IF(C2<>"",5,IF(D2<>"",2,"")))

The characters <>"" mean "Not equal to nothing"

What will happen is that as soon as anything is put in B2 (for Yes), a 10 will appear in E2. If B2 is empty and something is put in C2 (for No) a 5 will appear in E2 and a 2 will appear if A2 and B2 is empty, but a character is placed in D2. If all 3 cells are empty, E2 will be empty.

Note: The left most cell takes priority. i.e. if there is something in B2, it won't matter what is in C2 or D2, you'll still get 10. Excel will evaluate the formula from left to right and as soon as one of the IF's is TRUE, that value will be returned and Excel will stop looking.

You will need to create the fromula in each row changing the weighted values for each question as you see fit.

In column F of each row simply use =SUM(B2:D2)

Then in F19 use =SUM(F2:F18) to give the total score.

Note: Another perspective/way to use DerbyDad's formula is:
E2=IF(B2="Y",10,IF(C2="N",5,IF(D2="N/A",2,"")))
I personally would use a blank cell versus "N/A" so the correct answers would be either Y, N or leave it blank if not applicable. The formula could be modified to:
E2=IF(B2="Y",10,IF(C2="N",5,2))
N/A in D2 will still produce a score of "2".

...and yet another simplified method would be to enter either Y, N, N/A or leave blank in the single answer column (B in this example) and then using the formula in C2 instead as:
C2=IF(B2="Y",10,IF(B2="N",5,2)

Then in C19 use =SUM(C2:C18) to give the total score eliminating the need to sum accross the rows. (a little cleaner)

Regards,
Bryan

My approach would be as follows:

Column A is the question
Column B is the answer (use data valiadation to make it Y, N or X)
Column C is the "Y" value for the question
Column D is the "N" value for the question
Column E is the "X" value for the question

Your grand total is then

=SUMIF($B:$B,"Y",C:C)+SUMIF($B:$B,"N",D:D)+SUMIF($B:$B,"X",E:E)

One calculation which will probably end up being quicker than a whole load of IF() statements (depends on the number of questions, obviously)