| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Formulas with "Y" & "N" values
I am creating a performance chart for employees to fill out. Below is an example of a set of questions they need to complete, with a possible answer to each. My spreadsheet is being designed to calculate a points system on multiple categories 1. How many appointments did you have? "2" 2. How many new appointments did you set? "1" 3. Did you follow up with active client groups, not yet rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 5. Have you reviewed and replied to all your emails? "Y" I need a formula that will add up all the numeric answers, but also add the value for each question that has a "Y" or "N" possible ansewr. So question 3, answered "Y" may be +3 towards the total. but question 5 may only be a +1 towards the total if answered "Y" or a -3 if "N" please help! Much Thanks! Corey |
|
#2
|
|||
|
|||
|
I'll assume you have your questions in column A, and your
answers (Y/N)in column B. In column C, use the formula =IF (B1="Y",1,IF(B1="N",2)) then a "1" will be returned if the answer is "Y" or a "2" if the answer is a "N". Then, at the bottom of column C, simply add up the numbers. Hope this helps. -----Original Message----- I am creating a performance chart for employees to fill out. Below is an example of a set of questions they need to complete, with a possible answer to each. My spreadsheet is being designed to calculate a points system on multiple categories 1. How many appointments did you have? "2" 2. How many new appointments did you set? "1" 3. Did you follow up with active client groups, not yet rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 5. Have you reviewed and replied to all your emails? "Y" I need a formula that will add up all the numeric answers, but also add the value for each question that has a "Y" or "N" possible ansewr. So question 3, answered "Y" may be +3 towards the total. but question 5 may only be a +1 towards the total if answered "Y" or a -3 if "N" please help! Much Thanks! Corey . |
|
#3
|
|||
|
|||
|
Thanks for the suggestion but it is not working for me. I
am adding up C2:C7, but I want to stay within the same row when converting the "Y" or "N" to the value I set for it. In other words this is how I want it to work... C2 Y (value I set in formula = +1) C3 3 C4 N (value I set in formula = -2) C5 10 C6 Y (value I set in formula = +3) C7 2 C8 17 (Total) Any Ideas? Corey -----Original Message----- I'll assume you have your questions in column A, and your answers (Y/N)in column B. In column C, use the formula =IF (B1="Y",1,IF(B1="N",2)) then a "1" will be returned if the answer is "Y" or a "2" if the answer is a "N". Then, at the bottom of column C, simply add up the numbers. Hope this helps. -----Original Message----- I am creating a performance chart for employees to fill out. Below is an example of a set of questions they need to complete, with a possible answer to each. My spreadsheet is being designed to calculate a points system on multiple categories 1. How many appointments did you have? "2" 2. How many new appointments did you set? "1" 3. Did you follow up with active client groups, not yet rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 5. Have you reviewed and replied to all your emails? "Y" I need a formula that will add up all the numeric answers, but also add the value for each question that has a "Y" or "N" possible ansewr. So question 3, answered "Y" may be +3 towards the total. but question 5 may only be a +1 towards the total if answered "Y" or a -3 if "N" please help! Much Thanks! Corey . . |
|
#4
|
|||
|
|||
|
Can you email me the spreadsheet (or an example)?
-----Original Message----- Thanks for the suggestion but it is not working for me. I am adding up C2:C7, but I want to stay within the same row when converting the "Y" or "N" to the value I set for it. In other words this is how I want it to work... C2 Y (value I set in formula = +1) C3 3 C4 N (value I set in formula = -2) C5 10 C6 Y (value I set in formula = +3) C7 2 C8 17 (Total) Any Ideas? Corey -----Original Message----- I'll assume you have your questions in column A, and your answers (Y/N)in column B. In column C, use the formula =IF (B1="Y",1,IF(B1="N",2)) then a "1" will be returned if the answer is "Y" or a "2" if the answer is a "N". Then, at the bottom of column C, simply add up the numbers. Hope this helps. -----Original Message----- I am creating a performance chart for employees to fill out. Below is an example of a set of questions they need to complete, with a possible answer to each. My spreadsheet is being designed to calculate a points system on multiple categories 1. How many appointments did you have? "2" 2. How many new appointments did you set? "1" 3. Did you follow up with active client groups, not yet rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 5. Have you reviewed and replied to all your emails? "Y" I need a formula that will add up all the numeric answers, but also add the value for each question that has a "Y" or "N" possible ansewr. So question 3, answered "Y" may be +3 towards the total. but question 5 may only be a +1 towards the total if answered "Y" or a -3 if "N" please help! Much Thanks! Corey . . . |
|
#5
|
|||
|
|||
|
Thanks, I was able to figure this out it. This is it...
=SUM(C17+C19)+IF(C18="Y",1)+IF(C18="N",-2)+IF(C20="Y",1)+IF (C20="N",-2)+IF(C21="Y",1)+IF(C21="N",-2)+IF(C22="Y",1)+IF (C22="N",-2) -----Original Message----- Can you email me the spreadsheet (or an example)? -----Original Message----- Thanks for the suggestion but it is not working for me. I am adding up C2:C7, but I want to stay within the same row when converting the "Y" or "N" to the value I set for it. In other words this is how I want it to work... C2 Y (value I set in formula = +1) C3 3 C4 N (value I set in formula = -2) C5 10 C6 Y (value I set in formula = +3) C7 2 C8 17 (Total) Any Ideas? Corey -----Original Message----- I'll assume you have your questions in column A, and your answers (Y/N)in column B. In column C, use the formula =IF (B1="Y",1,IF(B1="N",2)) then a "1" will be returned if the answer is "Y" or a "2" if the answer is a "N". Then, at the bottom of column C, simply add up the numbers. Hope this helps. -----Original Message----- I am creating a performance chart for employees to fill out. Below is an example of a set of questions they need to complete, with a possible answer to each. My spreadsheet is being designed to calculate a points system on multiple categories 1. How many appointments did you have? "2" 2. How many new appointments did you set? "1" 3. Did you follow up with active client groups, not yet rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 5. Have you reviewed and replied to all your emails? "Y" I need a formula that will add up all the numeric answers, but also add the value for each question that has a "Y" or "N" possible ansewr. So question 3, answered "Y" may be +3 towards the total. but question 5 may only be a +1 towards the total if answered "Y" or a -3 if "N" please help! Much Thanks! Corey . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| fill formulas and values | Excel Discussion (Misc queries) | |||
| delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) | |||
| How do I view formulas by values not by which cells they use to c. | Excel Discussion (Misc queries) | |||
| Spreadsheet is only showing formulas not the actual values | Excel Worksheet Functions | |||
| Why do formulas turn to values wen copying in Excel 2003... | Excel Worksheet Functions | |||
Linear Mode
