Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then 1
I am trying to create a simple list in a cell: YES or NO. Then I want to
sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then 1
Try something like this:
With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
Hi Ron,
Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
Thanks for the feedback......I'm glad you could use that.
Regarding: May I ask you a second related question? Of course! What's the question? *********** Regards, Ron XL2002, WinXP "rrr" wrote: Hi Ron, Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
In the same row (where there are cells with YES and NO values) I would like
to incorporate a value from text in one additional cell. In that cell I've build a list with three word values: SIMPLE STANDARD or COMPLEX. If that cell is blank it should count as 0 similar to the blank or" NO" cell from before. But if the word in that cell is SIMPLE is should count as 1, if STANDARD then 2, but if COMPLEX s/b 1 again....same as SIMPLE. Any ideas? "Ron Coderre" wrote: Thanks for the feedback......I'm glad you could use that. Regarding: May I ask you a second related question? Of course! What's the question? *********** Regards, Ron XL2002, WinXP "rrr" wrote: Hi Ron, Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
Maybe something like this?:
For a value, or blank, in A1 B1: =LOOKUP(A1&"",{"","Complex","Simple","Standard"},{ 0,1,1,2}) Note: The list within the first set of braces { } must be in ascending sort order OR Alternatively, if there will be more than a few of these formulas you should drive the values via a table. Example: F1:F3 contains this list Complex Simple Standard G1:G3 contains this list 1 1 2 B1: =SUMPRODUCT(COUNTIF(A1,$F$1:$F$3)*$G$1:$G$3) Note: That may not be a standard solution, but it is durable against non-list entries, blanks, and numbers. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "rrr" wrote: In the same row (where there are cells with YES and NO values) I would like to incorporate a value from text in one additional cell. In that cell I've build a list with three word values: SIMPLE STANDARD or COMPLEX. If that cell is blank it should count as 0 similar to the blank or" NO" cell from before. But if the word in that cell is SIMPLE is should count as 1, if STANDARD then 2, but if COMPLEX s/b 1 again....same as SIMPLE. Any ideas? "Ron Coderre" wrote: Thanks for the feedback......I'm glad you could use that. Regarding: May I ask you a second related question? Of course! What's the question? *********** Regards, Ron XL2002, WinXP "rrr" wrote: Hi Ron, Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
Ron,
That is absolutely terrific. Thank you. I will apply this to my sheet and it will be much improved. Thank you sir! Randy PS Let me know if I can do anything more to attach positive feedback to you. "Ron Coderre" wrote: Maybe something like this?: For a value, or blank, in A1 B1: =LOOKUP(A1&"",{"","Complex","Simple","Standard"},{ 0,1,1,2}) Note: The list within the first set of braces { } must be in ascending sort order OR Alternatively, if there will be more than a few of these formulas you should drive the values via a table. Example: F1:F3 contains this list Complex Simple Standard G1:G3 contains this list 1 1 2 B1: =SUMPRODUCT(COUNTIF(A1,$F$1:$F$3)*$G$1:$G$3) Note: That may not be a standard solution, but it is durable against non-list entries, blanks, and numbers. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "rrr" wrote: In the same row (where there are cells with YES and NO values) I would like to incorporate a value from text in one additional cell. In that cell I've build a list with three word values: SIMPLE STANDARD or COMPLEX. If that cell is blank it should count as 0 similar to the blank or" NO" cell from before. But if the word in that cell is SIMPLE is should count as 1, if STANDARD then 2, but if COMPLEX s/b 1 again....same as SIMPLE. Any ideas? "Ron Coderre" wrote: Thanks for the feedback......I'm glad you could use that. Regarding: May I ask you a second related question? Of course! What's the question? *********** Regards, Ron XL2002, WinXP "rrr" wrote: Hi Ron, Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I assign a numeric value to text? IE cell = yes then
PS Let me know if I can do anything more to attach positive feedback to you.
Honestly, Randy, your appreciation is reward enough. Thanks for the kind words. *********** Sincerely, Ron XL2002, WinXP "rrr" wrote: Ron, That is absolutely terrific. Thank you. I will apply this to my sheet and it will be much improved. Thank you sir! Randy PS Let me know if I can do anything more to attach positive feedback to you. "Ron Coderre" wrote: Maybe something like this?: For a value, or blank, in A1 B1: =LOOKUP(A1&"",{"","Complex","Simple","Standard"},{ 0,1,1,2}) Note: The list within the first set of braces { } must be in ascending sort order OR Alternatively, if there will be more than a few of these formulas you should drive the values via a table. Example: F1:F3 contains this list Complex Simple Standard G1:G3 contains this list 1 1 2 B1: =SUMPRODUCT(COUNTIF(A1,$F$1:$F$3)*$G$1:$G$3) Note: That may not be a standard solution, but it is durable against non-list entries, blanks, and numbers. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "rrr" wrote: In the same row (where there are cells with YES and NO values) I would like to incorporate a value from text in one additional cell. In that cell I've build a list with three word values: SIMPLE STANDARD or COMPLEX. If that cell is blank it should count as 0 similar to the blank or" NO" cell from before. But if the word in that cell is SIMPLE is should count as 1, if STANDARD then 2, but if COMPLEX s/b 1 again....same as SIMPLE. Any ideas? "Ron Coderre" wrote: Thanks for the feedback......I'm glad you could use that. Regarding: May I ask you a second related question? Of course! What's the question? *********** Regards, Ron XL2002, WinXP "rrr" wrote: Hi Ron, Thank you very much. Works great. May I ask you a second related question? Randy "Ron Coderre" wrote: Try something like this: With A1:A20 containing "Yes" and "No" values This formula returns the count of "Yes" values B1: =COUNTIF(A1:A20,"Yes") Does that help? *********** Regards, Ron XL2002, WinXP "rrr" wrote: I am trying to create a simple list in a cell: YES or NO. Then I want to sum the row of YES or NO's. If I can assign a value of 1 to YES and 0 to NO I can do the math. Not sure how to assign a numeric value to a short list of text in a cell to enable this math operation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clicking cell containing text, and a numeric value appear in anoth | Excel Discussion (Misc queries) | |||
Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Assign values to text within a cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |