# minus numbers causing a problem

1. ## minus numbers causing a problem

I am trying to set up a 4 day "Scorecard" for a golf tournament using Excel
2002.

Column A is players name. column B is handicap.

Column C is Day1 Gross score, D is Day1 Net score and E is a daily running
Total. Repeated for the four days with Total Gross and Net scores as the last
two columns.

I want Column D to show the result of C -B and Column E to show the running
total Net scores.

I was wondering if there was a formula I could use that wouldn't show and
total the Minus figure generated at Column B.

I have managed to hide it using Conditional Formatting but it is still
subtracting the handicap in B therefore not giving a true daily result.

If you can understand this! Any help would be appreciated....Thanks

2. ## Re: minus numbers causing a problem

Perhaps in D2 something like

=IF(C2-B2<0,0,C2-B2)

translated.........
if C2-B2 smaller than zero, display zero, otherwise display C2-B2

Greetings from New Zealand
Bill K
"kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
>I am trying to set up a 4 day "Scorecard" for a golf tournament using Excel
> 2002.
>
> Column A is players name. column B is handicap.
>
> Column C is Day1 Gross score, D is Day1 Net score and E is a daily running
> Total. Repeated for the four days with Total Gross and Net scores as the
> last
> two columns.
>
> I want Column D to show the result of C -B and Column E to show the
> running
> total Net scores.
>
> I was wondering if there was a formula I could use that wouldn't show and
> total the Minus figure generated at Column B.
>
> I have managed to hide it using Conditional Formatting but it is still
> subtracting the handicap in B therefore not giving a true daily result.
>
> If you can understand this! Any help would be appreciated....Thanks
>
>

3. ## Re: minus numbers causing a problem

Thanks Bill, but it didn't help, just displayed "0"
Maybe this might give a better idea of what I'm after.

A B C D E F G H I J K
L M N

name handicap day1 day2 day3
day4
gr/net/total gr/net/total gr/net/total
gr/net/total

AB 20 117 97 97 124 104 201 100 80 281 98 78 359
Thanks again

"Bill Kuunders" wrote:

> Perhaps in D2 something like
>
> =IF(C2-B2<0,0,C2-B2)
>
> translated.........
> if C2-B2 smaller than zero, display zero, otherwise display C2-B2
>
>
> Greetings from New Zealand
> Bill K
> "kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
> >I am trying to set up a 4 day "Scorecard" for a golf tournament using Excel
> > 2002.
> >
> > Column A is players name. column B is handicap.
> >
> > Column C is Day1 Gross score, D is Day1 Net score and E is a daily running
> > Total. Repeated for the four days with Total Gross and Net scores as the
> > last
> > two columns.
> >
> > I want Column D to show the result of C -B and Column E to show the
> > running
> > total Net scores.
> >
> > I was wondering if there was a formula I could use that wouldn't show and
> > total the Minus figure generated at Column B.
> >
> > I have managed to hide it using Conditional Formatting but it is still
> > subtracting the handicap in B therefore not giving a true daily result.
> >
> > If you can understand this! Any help would be appreciated....Thanks
> >
> >

>
>
>

4. ## Re: minus numbers causing a problem

"kevhatch" wrote:

> Thanks Bill, but it didn't help, just displayed "0"
> Maybe this might give a better idea of what I'm after.
>
> A B C D E F G H I J K L M N
>
>
>name handicap day1 day2 day3 day4
>
> gr/net/total gr/net/total gr/net/total gr/net/total
>
>
> AB 20 117 97 97 124 104 201 100 80 281 98 78 359
> Thanks again
>
>
>
>
> "Bill Kuunders" wrote:
>
> > Perhaps in D2 something like
> >
> > =IF(C2-B2<0,0,C2-B2)
> >
> > translated.........
> > if C2-B2 smaller than zero, display zero, otherwise display C2-B2
> >
> >
> > Greetings from New Zealand
> > Bill K
> > "kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
> > >I am trying to set up a 4 day "Scorecard" for a golf tournament using Excel
> > > 2002.
> > >
> > > Column A is players name. column B is handicap.
> > >
> > > Column C is Day1 Gross score, D is Day1 Net score and E is a daily running
> > > Total. Repeated for the four days with Total Gross and Net scores as the
> > > last
> > > two columns.
> > >
> > > I want Column D to show the result of C -B and Column E to show the
> > > running
> > > total Net scores.
> > >
> > > I was wondering if there was a formula I could use that wouldn't show and
> > > total the Minus figure generated at Column B.
> > >
> > > I have managed to hide it using Conditional Formatting but it is still
> > > subtracting the handicap in B therefore not giving a true daily result.
> > >
> > > If you can understand this! Any help would be appreciated....Thanks
> > >
> > >

> >
> >
> >

5. ## Re: minus numbers causing a problem

> ... a formula I could use that wouldn't show and
> total the Minus figure generated at Column B.

the 1st row of data / formulas will be in row3, in A3:N3
(link to sample file provided below)

Try this:

D3: =IF(\$B3<0,C3,C3-\$B3)
E3: =D3

G3: =IF(\$B3<0,F3,F3-\$B3)
H3: =E3+G3

J3: =IF(\$B3<0,I3,I3-\$B3)
K3: =H3+J3

M3: =IF(\$B3<0,L3,L3-\$B3)
N3: =K3+M3

Select D3:N3 and copy down as many rows as needed

For a neat look, suppress extraneous zeros in the sheet via:
Click Tools > Options > Uncheck "Zero values" > OK

Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=68674

at the top in the page, just above the ads)

File: kevhatch_newusers_1.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

6. ## Re: minus numbers causing a problem

Clarification: In the suggested construct,
C3, F3, I3 & L3 are the input cells for
the gross scores for day1, day2, day3 & day4
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

7. ## Re: minus numbers causing a problem

"Max" wrote:

> Clarification: In the suggested construct,
> C3, F3, I3 & L3 are the input cells for
> the gross scores for day1, day2, day3 & day4
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
> Thx Max,

that sorted a lot of the problems out.
> I have one more.... with a blank score sheet, i.e. just the handicaps in column B, column N is returning a -value.... 4x handicap of 20. Is there any way I can get column N to just total all the "Net" scores as they are added day by day?

If I made all the "Net" scores the same colour, is there a formula for
totalling that?
Thanks again

8. ## Re: minus numbers causing a problem

First I would like to say that Max gave us a very clear answer.
I had a look at his sample file. Excellent

To solve the last question I used my original formula again.
Enter this on Max's spreadsheet in D11 and copy it to G11 J11 and M11
=IF(C11-\$B11<0,0,C11-\$B11)

--
Greetings from New Zealand
Bill K

"kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
news:25C2538E-3CE4-4E47-A024-76653E54DFEA@microsoft.com...
>
>
> "Max" wrote:
>
>> Clarification: In the suggested construct,
>> C3, F3, I3 & L3 are the input cells for
>> the gross scores for day1, day2, day3 & day4
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> GMT+8, 1° 22' N 103° 45' E
>> xdemechanik <at>yahoo<dot>com
>> ----
>>
>> Thx Max,

> that sorted a lot of the problems out.
>> I have one more.... with a blank score sheet, i.e. just the handicaps in
>> column B, column N is returning a -value.... 4x handicap of 20. Is there
>> any way I can get column N to just total all the "Net" scores as they are

> If I made all the "Net" scores the same colour, is there a formula for
> totalling that?
> Thanks again

9. ## Re: minus numbers causing a problem

You can of course enter another if- function if you would
like to show empty cells in the subtotals of the next rounds
untill there is a gross score for that round.

for example
=IF(F11=0,0,E11+G11)
etc.

--
Greetings from New Zealand
Bill K

"Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
news:ueY2162dFHA.2420@TK2MSFTNGP15.phx.gbl...
> First I would like to say that Max gave us a very clear answer.
> I had a look at his sample file. Excellent
>
> To solve the last question I used my original formula again.
> Enter this on Max's spreadsheet in D11 and copy it to G11 J11 and M11
> =IF(C11-\$B11<0,0,C11-\$B11)
>
>
> --
> Greetings from New Zealand
> Bill K
>
> "kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
> news:25C2538E-3CE4-4E47-A024-76653E54DFEA@microsoft.com...
>>
>>
>> "Max" wrote:
>>
>>> Clarification: In the suggested construct,
>>> C3, F3, I3 & L3 are the input cells for
>>> the gross scores for day1, day2, day3 & day4
>>> --
>>> Rgds
>>> Max
>>> xl 97
>>> ---
>>> GMT+8, 1° 22' N 103° 45' E
>>> xdemechanik <at>yahoo<dot>com
>>> ----
>>>
>>> Thx Max,

>> that sorted a lot of the problems out.
>>> I have one more.... with a blank score sheet, i.e. just the handicaps in
>>> column B, column N is returning a -value.... 4x handicap of 20. Is
>>> there any way I can get column N to just total all the "Net" scores as
>>> they are added day by day?

>> If I made all the "Net" scores the same colour, is there a formula for
>> totalling that?
>> Thanks again

>
>

10. ## Re: minus numbers causing a problem

"kevhatch" wrote
> > Thx Max, that sorted a lot of the problems out.

> > I have one more.... with a blank score sheet, i.e. just the handicaps in

column B, column N is returning a -value.... 4x handicap of 20. Is there
any way I can get column N to just total all the "Net" scores as they are

Try these revisions to achieve the progressive visual effects required
(link to revised sample file below):

D3: =IF(C3="",0,IF(\$B3<0,C3,C3-\$B3))
E3: =IF(C3="",0,D3)

G3: =IF(F3="",0,IF(\$B3<0,F3,F3-\$B3))
H3: =IF(F3="",0,E3+G3)

J3: =IF(I3="",0,IF(\$B3<0,I3,I3-\$B3))
K3: =IF(I3="",0,H3+J3)

M3: =IF(L3="",0,IF(\$B3<0,L3,L3-\$B3))
N3: =IF(L3="",0,K3+M3)

Create a new col for a "Cumulative Total"

Put in O3:

=IF(AND(E3<>0,H3<>0,K3<>0,N3<>0),N3,IF(AND(E3<>0,H3<>0,K3<>0,N3=0),K3,IF(AND
(E3<>0,H3<>0,K3=0,N3=0),H3,IF(AND(E3<>0,H3=0,K3=0,N3=0),E3,0))))

Select D3:O3 and copy down as many rows as needed

> If I made all the "Net" scores the same colour, is there a formula for

totalling that?

Totalling by cell fill colour requires VBA, which makes things a lot more
complex than needed in this instance. The above revised formulas should
achieve the effects that you seek.

Here's the revised sample file with the implemented construct:
http://flypicture.com/p.cfm?id=69180

at the top in the page, just above the ads)

File: kevhatch_newusers_2.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

11. ## Re: minus numbers causing a problem

Glad the sample file was of use, Bill <g>
Thanks !

From the OP's lines:

> ... a formula I could use that wouldn't show and
> total the Minus figure generated at Column B.

it appears that there's a formula? in col B generating the handicaps, and
negative figures could result. I think the OP doesn't want the handicap
figures, if negative, to be computed. That's why I suggested those earlier
formulas to ignore negative handicap figures.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

12. ## Re: minus numbers causing a problem

Belated typo correction:

Line:
> Click Tools > Options > Uncheck "Zero values" > OK

Click Tools > Options > View tab > Uncheck "Zero values" > OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

13. ## Re: minus numbers causing a problem

"Max" wrote:

> Belated typo correction:
>
> Line:
> > Click Tools > Options > Uncheck "Zero values" > OK

>
> Click Tools > Options > View tab > Uncheck "Zero values" > OK
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
> Big Thanks to Bill and Max for your help guys, I could never have figured those formulas out myself in a month of Sundays!!
>

14. ## Re: minus numbers causing a problem

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kevhatch" wrote
> .. Big Thanks to Bill and Max for your help guys,
> I could never have figured those formulas out
> myself in a month of Sundays!!

15. ## Re: minus numbers causing a problem

Here's a new link to the revised sample file
with the implemented construct:
http://www.savefile.com/files/5421416
File: kevhatch_newusers_2.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1