MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### blank cells having no value

• Follow

```Hi,
though this is Steve's account it is his wife Val writing with my problem.
Back in the old days of DOS there was a function which allowed you to set all
blank cells as blank - that is = not having a value of Zero.
I am working on Excel 2003 at home and 2007 at work so am at the moment
somewhat confused and cannot find that facility on either system.

What I am trying to do is:
I have two cells  one E6 representing "goals For" and another F6 representing
"goals against"
I have the following simple formula.  =IF(e6>F6,3,IF(e6=F6,1,0))
this works fine when the figures are in and gives correct figures for a win
draw and loss but because it continues to see blank cells as having zero value
keeps returning a 1 even though there is nothing there.
Can anyone tell me either where I can find the facility to turn off the zero
value on a blank cell or offer an alternative solution.  e.g 0-0 is still a
draw and worth 1 point.
regards
Val
--
Steve Hayes from Tshwane, South Africa
Web:  http://hayesfam.bravehost.com/stevesig.htm
Blog: http://methodius.blogspot.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk
```
 0

```You could check for scores in the cells to start:

=if(count(e6:f6)<2,"",if(e6>f6, ....

Steve Hayes wrote:
>
> Hi,
> though this is Steve's account it is his wife Val writing with my problem.
> Back in the old days of DOS there was a function which allowed you to set all
> blank cells as blank - that is = not having a value of Zero.
> I am working on Excel 2003 at home and 2007 at work so am at the moment
> somewhat confused and cannot find that facility on either system.
>
> What I am trying to do is:
> I have two cells  one E6 representing "goals For" and another F6 representing
> "goals against"
> I have the following simple formula.  =IF(e6>F6,3,IF(e6=F6,1,0))
> this works fine when the figures are in and gives correct figures for a win
> draw and loss but because it continues to see blank cells as having zero value
> keeps returning a 1 even though there is nothing there.
> Can anyone tell me either where I can find the facility to turn off the zero
> value on a blank cell or offer an alternative solution.  e.g 0-0 is still a
> draw and worth 1 point.
> regards
> Val
> --
> Steve Hayes from Tshwane, South Africa
> Web:  http://hayesfam.bravehost.com/stevesig.htm
> Blog: http://methodius.blogspot.com
> E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

--

Dave Peterson
```
 0

```hello Steve's wife Val,

Try this

=IF(COUNT(E6:F6)<2,"",IF(E6>F6,3,IF(E6=F6,1,0)))

Now both cells must be populated for the formula to evaluate
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Steve Hayes" wrote:

> Hi,
> though this is Steve's account it is his wife Val writing with my problem.
> Back in the old days of DOS there was a function which allowed you to set all
> blank cells as blank - that is = not having a value of Zero.
> I am working on Excel 2003 at home and 2007 at work so am at the moment
> somewhat confused and cannot find that facility on either system.
>
> What I am trying to do is:
> I have two cells  one E6 representing "goals For" and another F6 representing
> "goals against"
> I have the following simple formula.  =IF(e6>F6,3,IF(e6=F6,1,0))
> this works fine when the figures are in and gives correct figures for a win
> draw and loss but because it continues to see blank cells as having zero value
> keeps returning a 1 even though there is nothing there.
> Can anyone tell me either where I can find the facility to turn off the zero
> value on a blank cell or offer an alternative solution.  e.g 0-0 is still a
> draw and worth 1 point.
> regards
> Val
> --
> Steve Hayes from Tshwane, South Africa
> Web:  http://hayesfam.bravehost.com/stevesig.htm
> Blog: http://methodius.blogspot.com
> E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk
> .
>
```
 0

2 Replies
651 Views

8/24/2012 8:36:37 AM