#### Zeros in worksheet

```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

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.

```
 0
DrBryanJ (3)
10/8/2003 3:41:09 PM
excel.misc 78881 articles. 5 followers.

5 Replies
399 Views

Similar Articles

[PageSpeed] 58

```"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
>
> 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.

```
 0
Paul
10/8/2003 3:50:54 PM
```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
>
> 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.
>

```
 0
frank8273 (76)
10/8/2003 3:59:43 PM
```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.

```
 0
DrBryanJ (3)
10/8/2003 4:01:39 PM
```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
> >>
> >> 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.
> >>
> >
> >
> >.
> >

```
 0
andy2763 (218)
10/8/2003 4:14:08 PM
```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
>> >>
>> >> 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.
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>
```
 0
Drbryanj
10/8/2003 4:22:31 PM

Similar Artilces:

worksheet sliding when cell is tabbed
Good morning, i have a problem where i select a cell and by using right or left arrow the work sheet move rather than the cell, normally when you tab a cell the worksheet would remain in place until you reach the last viewable column, them the entire sheet would move, this time if i select cell A1 and right arrow Column A dissapear to the left. Can anyone help to fix this? SCROLL LOCK is ON. Turn that OFF. -- Jacob (MVP - Excel) "whatzzup" wrote: > Good morning, i have a problem where i select a cell and by using right or > left arrow the work sheet mo...

Create a template for every worksheet
I need to create a template that can be updated later for 80-some worksheets in a workbook. Basically, each tab has some information in common (which is updated every year) but some information which is unique. In the past, I just created the first worksheet without any unique information, and copied/pasted to new worksheets. The problem is, if I need to update something on all of them, I have to go back to everyone individually and copy/paste. Is there a way to make a template that will automatically update all worksheets based on it? Thanks! ...

Replace cell of column with cells of other worksheet
Hello, I have the following problem. In one sheet I have a table with imported keys from an access table. Now I want to replace these keys with strings that are in a second worksheet. So eg when a cell in the 1st sheet has value 2 it has to be replaced by eg "the second"... How can I do this for all the cells of that column automatically? Thanks a lot. Kristof I'd suggest VLOOKUP, if it weren't for > all the cells of that column > automatically Find & Replace is probably out, since what works for "2" will affect 12, 20, 21, 22 ... Depending ...

Multiple worksheets with non consecutive dates
I have 10 worksheets per workbook. Each worksheet has the same parameters of which I would like to trend. However due to the dates in each worksheet differing (and non consecutive), the chart accepts the dates of the first worksheet as the x-axis and plots all subsequent worksheets in sequential order For exampl Worksheet 1 Worksheet date, paramete Jan 1 44 Jan 29 2 Jan 4 55 Feb 35 8 The chart would use Jan1 and Jan 4 on the x axis and plot worksheet 2 values in the first (Jan 1) and the second (Jan 4) values on the x axis regards Watergirl...

Extracting data from a worksheet based on a range of in a column.
Alright, This is my first post to the group!! Here is what I have to get it started: Worksheet(Manuf#) 26025 records (Product#, Description,Buyline, Manuf#) Worksheet(Products) various amount of product ID's in column A(only column of data), this all I want to have in this worksheet, just the list of products I want to find from Manuf#. So what I need is a macro, Module, whatever it will take so I can look at the Products in Worksheet(Products) and extract just those Records from Worksheet(Manuf#) into a new Worksheet named Results? Any help at this time would be greatly appreciated. ...

Insert image in a protected Excel worksheet
We are using Excel 2000 and are facing a problem. The worksheet is password protected. There is an unprotected cell for user signature. The user is able to type in their name. But not able to paste a Jpeg or any image file. How can I fix this? ...

How do I stop 'template help' from opening in a worksheet?
When I open a workbook, I do not want to see 'Template Help.' 2003 version Help>Online Content. Uncheck "show template help when available" 2007 version.....no idea but willing to learn. Gord Dibben MS Excel MVP On Sat, 29 May 2010 08:08:01 -0700, Goldenbarstewart <Goldenbarstewart@discussions.microsoft.com> wrote: >When I open a workbook, I do not want to see 'Template Help.' ...

How to hide worksheet columns & prevent unhiding for certain users...
I have a worksheet with hourly rate column and hours columns. Because of the confidentiality of the hourly rate so I would like to hide the rate column but still able to allow my team to enter hours. Sounds like I really want to prevent unauthorized persons to un-hike the rate column but still allow them to update hours columns. Is it somethig do-able in excel? Thanks in advance! Wei Wu. p.s. unfortunately I cannot redesign the workbook to separate sensitive information in the separate workshet. Hi - select the cells for which you want allow entries - goto 'Format - cells - Protect...

Dynamic Excel Worksheet on our Portal for Non-CRM Users to View
I wish to share certain information out of CRM with users who are not CRM users. I know I can create a spreadsheet and post the spreadsheet, but then we have to manually update the spreadsheet on a regular basis for this to have any value. The concept I came up with was to create a dynamic worksheet in Excel, format the view the way I want it, and upload it to the portal. That works great for anyone who is a CRM user, but doesn't work at all for those who are not CRM users. I'm not sure if I have a CRM issue or an Excel issue. How can I create a worksheet that displays the ...

Compare 2 worksheets #2
How do I do a side by side comparison of two worksheets? If you are using Excel 2003 use "Window>Compare side by side with..." For other versions of Excel check the following knowledge base article http://support.microsoft.com/?kbid=119718 Tony kjones wrote: > How do I do a side by side comparison of two worksheets? ...

Shuts Down When Copying Worksheets
When I right-click on a worksheet tab, choose create a copy and tha past it into a new WORKBOOK Excel completely shuts down -- Message posted from http://www.ExcelForum.com I had that happen once to one of my workbooks. It was just one worksheet in a workbook with many worksheets. My first workaround was to save the original workbook (with the problem sheet). Right click, move or copy, but choose MOVE. excel didn't crash. Close the original workbook without saving. This worked ok for me, but scared the heck out of me. I was using xl97 at work where the problem occurred, and xl2002 a...

My worksheets will not maximize.
hitting the maximize button just toggles between two difference sizes, neither of which is maximized. There is only one worksheet open, no duplicates, no hidden worksheets, etc. ...

copy worksheet containing form
I have several worksheets which I use to print delivery forms that have been provided by vendors for national accounts. Each time the vendor changes a different workbook must be opened. What I would like to do is consolidate all these forms into one workbook. How can I copy the entire worksheet including all spacing, merged cells, etc into a different workbook? Start a new workbook. Open up one of the existing workbooks. Select the sheets you want to copy into your new workbook. (Click on the first and ctrl-click on subsequent) Then Edit|Move or Copy Sheet Select your new book in the &qu...

how do I input zip codes with a leading zero
I live in the NE where zip codes start with a zero. I have gone in and tried all options under format cells. I even entered some coding that would let me input what I need and it did not work. I am trying to set up a mail merge in Microsoft Word (I have Microsoft Office 2003 Standard Edition) and all my zips show up without the leading zero. Please help! format --text Tried that, thanks... still does not work! "º¼ÖÝÈË" wrote: > format --text > > > If you format the cell as text, then type in your zip code (with the leading zero...

max rows on an Excel worksheet
I thought the maximum number of rows available on an Excel spreadsheet was 65,536. But recently I was told the number of rows could be increased by decreasing the number of columns. Is that true? If so, how is it done. Sorry if this question has been asked before, I tried searching the site for others who might have asked this question, but the search feature is off. vbJenny Hi Sorry, your informant is incorrect. 65,536 is all you get. One option if you need more is to spread your data over a number of sheets and create a summary sheet to bring totals an other information back t...

Hide Cells with Zero Value in Excel 2007 while using line chart ty
Hello All, I am using Excel 2007 and have a situation with the charting functionality. For example - I am trying to set up automatic update chart update for Jan 08 – Dec 08 data i.e. user just enters data each month and the graph trend shows up each month. I have done this a lot of times but seem to have a unique situation with excel 07 this time when I am using the line chart type to show the trend. The date cells that have some kind of formulas used i.e. if function or Iserror function etc. I tried to test the chart by entering data for January it works fine with bar graph only displ...

I need to set up a workbook with one worksheet per month for twelve months. There will be formulas in each worksheets, two of which need to carry forward totals from the previous month's worksheet. How do I link worksheets in this way? Start at the latest sheet and click on the cell where you want the previous sheet's data to appear, enter = and then go to the sheet that has the formula you want to carry forward. Click on the cell with the formula you want carried over and click enter. This should have the data you are looking for. "windsong" wrote: > I need t...

Worksheet event help #2
Have the following in a sheet code that i would like to activate when the enter key is pressed in B5. tried the following, but no success, the 1st part i was hoping would be the change event that would run a sorting macro in the 2nd part. any help??? (1st Part) Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address(0, 0) = "b5" Then (2nd Part) Application.ScreenUpdating = False ActiveWindow.FreezePanes = False Range("A4").Select ActiveWindow.SmallScroll Down:=234 Range("A4:B273").Select Selection.Sort Key1...

Linking rows of data to another worksheet
Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

Formatting Excel Worksheet
Please help. I have a spreadsheet which has various negative numbers entered as follows: e.g. 5.25- 0.32- 1,502.33- etc etc Is there any way to format it or a formula which will change the above numbers so that it is a negtive number e.g. -5.25, -0.32, - 1,502.33 etc etc I have tried all the usual ways to format it but the numbers still remain as 5.25- etc etc this should do it Sub fixneg() For Each c In Selection If Right(c, 1) = "-" Then c.Value = "-" & Left(c, Len(c) - 1) Next c End Sub -- Don Guillett SalesAid Software donaldb@281.com "KI" <KI@dis...

Vary column width and row height in the same worksheet
In Word, I can create two tables with different column width, then remove line to combine the two tables together. Can I achieve the same different column width and row height in the same worksheet? Either one on top of the other, or side by side?!! In XL, height and width are properties of entire rows and columns, so you can't achieve what you're looking for exactly. There are workarounds. Among them: - you can merge cells so that they appear to be wider/taller than their surrounding rows/columns. I don't recommend this as merged cells typically interfere with formattin...