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

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

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

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

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

With the headers in A1:N2,
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

(Right-click on the link: "Download File"
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 <atyahoo<dotcom
----

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 <atyahoo<dotcom
----

"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 <atyahoo<dotcom
----

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

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" wrote in message
...


"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 <atyahoo<dotcom
----

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

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" wrote in message
...
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" wrote in message
...


"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 <atyahoo<dotcom
----

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

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

Glad to hear that !

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?

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,H 3<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

(Right-click on the link: "Download File"
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 <atyahoo<dotcom
----

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 <atyahoo<dotcom
----

Belated typo correction:

Line:
Click Tools Options Uncheck "Zero values" OK

should read:
Click Tools Options View tab Uncheck "Zero values" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote:

Belated typo correction:

Line:
Click Tools Options Uncheck "Zero values" OK

should read:
Click Tools Options View tab Uncheck "Zero values" OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Big Thanks to Bill and Max for your help guys, I could never have figured those formulas out myself in a month of Sundays!!

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"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!!

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 <atyahoo<dotcom
----