|
I need help assigning a value to a character
I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 |
in cell a11 type in
=SUM(IF(A1:A10="Y",1,0)) as one line; DO NOT PRESS ENTER but instead press simultaneously the CONTROL KEY + SHIFTKEY+ENTER This will create a Control array formula (CSE). HTH "billybob" wrote in message ... I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 |
BillyBob,
If the cell contains a Y, it contains a Y. You'd use an IF in a formula elsewhere that want's to know, like: =IF(A1 = "Y", 1, 0) That one isn't case-sensitive. For upper-case Y only, use: =IF( CODE(A1) = 89, 1, 0) -- Earl Kiosterud www.smokeylake.com "billybob" wrote in message ... I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 |
I don't think you'll succeed with formatting alone.
But you could use a helper cell: =if(a1="y",1,0) You could put this in column B and hide that column, but use B1 for the subsequent formulas. billybob wrote: I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 -- Dave Peterson |
Just this portion:
=IF( CODE(A1) = 89, 1, 0) Maybe: =IF(CODE(A1) = code("Y"), 1, 0) or to avoid an error if A1 is empty: =IF(CODE(A1&" ") = code("Y"), 1, 0) Just a little easier to read (for me anyway). And one more way: =if(EXACT(A1,"Y")=True,1,0) or =--EXACT(A1,"Y") Earl Kiosterud wrote: BillyBob, If the cell contains a Y, it contains a Y. You'd use an IF in a formula elsewhere that want's to know, like: =IF(A1 = "Y", 1, 0) That one isn't case-sensitive. For upper-case Y only, use: =IF( CODE(A1) = 89, 1, 0) -- Earl Kiosterud www.smokeylake.com "billybob" wrote in message ... I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 -- Dave Peterson |
Dave,
Oops. You're absolutely right. I didn't test it with an empty cell. We can shorten =IF(EXACT(A1,"Y")=TRUE,1,0) to =IF(EXACT(A1,"Y"),1,0) =--EXACT(A1,"Y") is very cool, but a bit more cryptic for some. The real heck of it is that the OP may realize that demanding an upper-case Y may cause problems when users use lower-case, and he'll change the requirement. After all our work, we'll wind up with good old =IF(A1 = "Y", 1, 0) :) -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... Just this portion: =IF( CODE(A1) = 89, 1, 0) Maybe: =IF(CODE(A1) = code("Y"), 1, 0) or to avoid an error if A1 is empty: =IF(CODE(A1&" ") = code("Y"), 1, 0) Just a little easier to read (for me anyway). And one more way: =if(EXACT(A1,"Y")=True,1,0) or =--EXACT(A1,"Y") Earl Kiosterud wrote: BillyBob, If the cell contains a Y, it contains a Y. You'd use an IF in a formula elsewhere that want's to know, like: =IF(A1 = "Y", 1, 0) That one isn't case-sensitive. For upper-case Y only, use: =IF( CODE(A1) = 89, 1, 0) -- Earl Kiosterud www.smokeylake.com "billybob" wrote in message ... I need to format a cell so that if I enter an upper case "Y" it displays the "Y" but the cell thinks the value of the "Y" is "1" so that it can be added in another function elsewhere. I have tried to use the "IF" functions but have had no success as of yet. HELP! -- billybob ------------------------------------------------------------------------ billybob's Profile: http://www.excelforum.com/member.php...o&userid=26610 View this thread: http://www.excelforum.com/showthread...hreadid=398817 -- Dave Peterson |
| All times are GMT +1. The time now is 05:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com