Zeros in worksheet

  • Thread starter Thread starter DrBryanJ
  • Start date Start date
D

DrBryanJ

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 said:
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.
Click to expand...

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.
 
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
 
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.
 
Back
Top