Home |
Search |
Today's Posts |
#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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
... 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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equation setup problem | Excel Discussion (Misc queries) | |||
numbers formating problem | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Text & Numbers problem | Excel Worksheet Functions | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) |