minus numbers causing a problem

[kevhatch]

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

 

[Bill Kuunders]

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]

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

 

[kevhatch]

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

 

[Max]

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

 

[Max]

Clarification: In the suggested construct,
C3, F3, I3 & L3 are the input cells for
the gross scores for day1, day2, day3 & day4

 

[kevhatch]

Clarification: In the suggested construct,
C3, F3, I3 & L3 are the input cells for
the gross scores for day1, day2, day3 & day4

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

 

[Bill Kuunders]

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)

 

[Bill Kuunders]

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.

 

[Max]

Glad to hear that !
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,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

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: kevhatch_newusers_2.xls

 

[Max]

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.

 

[Max]

Belated typo correction:

Line:

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

should read:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

 

[kevhatch]

Belated typo correction:

Line:

should read:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[Max]

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