Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kevhatch
 
Posts: n/a
Default 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   Report Post  
Bill Kuunders
 
Posts: n/a
Default

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




  #3   Report Post  
kevhatch
 
Posts: n/a
Default

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





  #4   Report Post  
kevhatch
 
Posts: n/a
Default



"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





  #5   Report Post  
Max
 
Posts: n/a
Default

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




  #6   Report Post  
Max
 
Posts: n/a
Default

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


  #7   Report Post  
kevhatch
 
Posts: n/a
Default



"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
  #8   Report Post  
Bill Kuunders
 
Posts: n/a
Default

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



  #9   Report Post  
Bill Kuunders
 
Posts: n/a
Default

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





  #10   Report Post  
Max
 
Posts: n/a
Default

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




  #11   Report Post  
Max
 
Posts: n/a
Default

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


  #12   Report Post  
Max
 
Posts: n/a
Default

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


  #13   Report Post  
kevhatch
 
Posts: n/a
Default



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

  #14   Report Post  
Max
 
Posts: n/a
Default

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



  #15   Report Post  
Max
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Equation setup problem Hansel Excel Discussion (Misc queries) 2 June 21st 05 02:31 AM
numbers formating problem barsha Excel Discussion (Misc queries) 2 May 25th 05 11:45 AM
Count and Sum Total occurrances of two specific numbers Sam via OfficeKB.com Excel Worksheet Functions 10 March 29th 05 08:13 PM
Text & Numbers problem Trying to excel in life but need help Excel Worksheet Functions 1 March 6th 05 05:15 AM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 06:41 PM


All times are GMT +1. The time now is 09:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"