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