Home » Category » Microsoft Excel

Microsoft Excel: Zeros in worksheet

205| Thu, 22 May 2008 23:47:00 GMT| anonymous| Comments (5)
I am creating a work book to look at scores of a soccer
tournament. I created a function to look at the scores
and assign points to each team. This is the function: =IF
(G12="-",0,IF(G12>H12,3,IF(G12=H12,1,0))) The function
looks at the score of the soccer game and determines the
number of points a team recieves toward their standings
in the tournament. It should return a value of 3 if Team
g wins the game, a value of 1 if there is a tie and a
value of 0 if team h wins. The first part g12="-" works
fine it returns a value of 0 when no score is entered
yet. It is the G12>H12 that is the problem. When I type
a score of 0 - 3 the worksheet chenges the 0 to text form
zero. When the function looks at the binary values it
sees zero as greater than 1 and returns a value of 3
instead of 0.

I need the cell that I enter the score into to display
the number 0 instead of the text zero.

I thought it was a simple format option, but none of the
number formats seem to help.

Keywords & Tags: zeros, worksheet, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/495045/
 
«« Prev - Next »» 5 helpful answers below.
"DrBryanJ" <DrBryanj...optonline.net> wrote in message
news:01ae01c38db2$98692ab0$a101280a...phx.gbl...
> I am creating a work book to look at scores of a soccer
> tournament. I created a function to look at the scores
> and assign points to each team. This is the function: =IF
> (G12="-",0,IF(G12>H12,3,IF(G12=H12,1,0))) The function
> looks at the score of the soccer game and determines the
> number of points a team recieves toward their standings
> in the tournament. It should return a value of 3 if Team
> g wins the game, a value of 1 if there is a tie and a
> value of 0 if team h wins. The first part g12="-" works
> fine it returns a value of 0 when no score is entered
> yet. It is the G12>H12 that is the problem. When I type
> a score of 0 - 3 the worksheet chenges the 0 to text form
> zero. When the function looks at the binary values it
> sees zero as greater than 1 and returns a value of 3
> instead of 0.
> I need the cell that I enter the score into to display
> the number 0 instead of the text zero.
> I thought it was a simple format option, but none of the
> number formats seem to help.
>

Where are you typing the score, and what are you typing? If you actually
type 0 - 3 into one cell, it will inevitably be text as it contains the
hyphen character. You ought to be putting 0 in one cell (say A1) and 3 in
another (say B1). Then the score for each team would always be a number, and
you could use
=IF(A1>B1,3,IF(A1=B1,1,0))
I'm not sure what your test for "-" is trying to achieve.

paul | Thu, 22 May 2008 23:48:00 GMT |

Your formula works fine for me in a clean worksheet. Perhaps there's
something else going on in your worksheet/workbook that's causing strange
behavior. Try a new, clean worksheet and see if you get the results you
expect.
--
HTH -
-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com

"DrBryanJ" <DrBryanj...optonline.net> wrote in message
news:01ae01c38db2$98692ab0$a101280a...phx.gbl...
> I am creating a work book to look at scores of a soccer
> tournament. I created a function to look at the scores
> and assign points to each team. This is the function: =IF
> (G12="-",0,IF(G12>H12,3,IF(G12=H12,1,0))) The function
> looks at the score of the soccer game and determines the
> number of points a team recieves toward their standings
> in the tournament. It should return a value of 3 if Team
> g wins the game, a value of 1 if there is a tie and a
> value of 0 if team h wins. The first part g12="-" works
> fine it returns a value of 0 when no score is entered
> yet. It is the G12>H12 that is the problem. When I type
> a score of 0 - 3 the worksheet chenges the 0 to text form
> zero. When the function looks at the binary values it
> sees zero as greater than 1 and returns a value of 3
> instead of 0.
> I need the cell that I enter the score into to display
> the number 0 instead of the text zero.
> I thought it was a simple format option, but none of the
> number formats seem to help.
>

frank | Thu, 22 May 2008 23:49:00 GMT |

I am entering the score into two seperate cells. When I
enter the number 0 and move to another cell or hit enter
the program changes the entry to "zero" it will not leave
it as "0".

As for the "-". That is entered in the score s table
until the game has been played. Our scoring format
awards an additional point for a shut out and if I don't
have anything entered, the points total in incorrect. By
using - and a function to return 0 when it see -, the
totals are correct.

drbryanj | Thu, 22 May 2008 23:50:00 GMT |

Check Tools/AutoCorrect for an entry that changes 0 to zero

Andy.

"drbryanj" <drbryanj...optonline.net> wrote in message
news:011f01c38db6$89d99c10$a301280a...phx.gbl...
> The problem is not the function.
> The problem is that excel changes a typed entry of 0 to
> the "zero". The function then uses "zero" instead
> of "0". I may be wrong , but I believe the binary code
> for z is greater than any binary code for a number there
> for the function will always see the zero as greater and
> awards the wrong team 3 points.
> How can I get the entry to stay "0"?
> >--Original Message--
> >Your formula works fine for me in a clean worksheet.
> Perhaps there's
> >something else going on in your worksheet/workbook
> that's causing strange
> >behavior. Try a new, clean worksheet and see if you get
> the results you
> >expect.
> >--
> >HTH -
> >
> >-Frank Isaacs
> >Dolphin Technology Corp.
> >http://vbapro.com
> >
> >
> >"DrBryanJ" <DrBryanj...optonline.net> wrote in message
> >news:01ae01c38db2$98692ab0$a101280a...phx.gbl...
> >> I am creating a work book to look at scores of a soccer
> >> tournament. I created a function to look at the scores
> >> and assign points to each team. This is the function:
> =IF
> >> (G12="-",0,IF(G12>H12,3,IF(G12=H12,1,0))) The
> function
> >> looks at the score of the soccer game and determines
> the
> >> number of points a team recieves toward their standings
> >> in the tournament. It should return a value of 3 if
> Team
> >> g wins the game, a value of 1 if there is a tie and a
> >> value of 0 if team h wins. The first part g12="-" works
> >> fine it returns a value of 0 when no score is entered
> >> yet. It is the G12>H12 that is the problem. When I
> type
> >> a score of 0 - 3 the worksheet chenges the 0 to text
> form
> >> zero. When the function looks at the binary values it
> >> sees zero as greater than 1 and returns a value of 3
> >> instead of 0.
> >>
> >> I need the cell that I enter the score into to display
> >> the number 0 instead of the text zero.
> >>
> >> I thought it was a simple format option, but none of
> the
> >> number formats seem to help.
> >>
> >
> >
> >.
> >

andy | Thu, 22 May 2008 23:51:00 GMT |

Thank you. That did it.

Bryan
>--Original Message--
>Check Tools/AutoCorrect for an entry that changes 0 to
zero
>Andy.
>"drbryanj" <drbryanj...optonline.net> wrote in message
>news:011f01c38db6$89d99c10$a301280a...phx.gbl...
>> The problem is not the function.
>> The problem is that excel changes a typed entry of 0 to
>> the "zero". The function then uses "zero" instead
>> of "0". I may be wrong , but I believe the binary code
>> for z is greater than any binary code for a number
there
>> for the function will always see the zero as greater
and
>> awards the wrong team 3 points.
>> How can I get the entry to stay "0"?
>> >--Original Message--
>> >Your formula works fine for me in a clean worksheet.
>> Perhaps there's
>> >something else going on in your worksheet/workbook
>> that's causing strange
>> >behavior. Try a new, clean worksheet and see if you
get
>> the results you
>> >expect.
>> >--
>> >HTH -
>> >
>> >-Frank Isaacs
>> >Dolphin Technology Corp.
>> >http://vbapro.com
>> >
>> >
>> >"DrBryanJ" <DrBryanj...optonline.net> wrote in message
>> >news:01ae01c38db2$98692ab0$a101280a...phx.gbl...
>> >> I am creating a work book to look at scores of a
soccer
>> >> tournament. I created a function to look at the
scores
>> >> and assign points to each team. This is the
function:
>> =IF
>> >> (G12="-",0,IF(G12>H12,3,IF(G12=H12,1,0))) The
>> function
>> >> looks at the score of the soccer game and determines
>> the
>> >> number of points a team recieves toward their
standings
>> >> in the tournament. It should return a value of 3 if
>> Team
>> >> g wins the game, a value of 1 if there is a tie and
a
>> >> value of 0 if team h wins. The first part g12="-"
works
>> >> fine it returns a value of 0 when no score is
entered
>> >> yet. It is the G12>H12 that is the problem. When I
>> type
>> >> a score of 0 - 3 the worksheet chenges the 0 to text
>> form
>> >> zero. When the function looks at the binary values
it
>> >> sees zero as greater than 1 and returns a value of 3
>> >> instead of 0.
>> >>
>> >> I need the cell that I enter the score into to
display
>> >> the number 0 instead of the text zero.
>> >>
>> >> I thought it was a simple format option, but none of
>> the
>> >> number formats seem to help.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

drbryanj | Thu, 22 May 2008 23:52:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories