+ Reply to Thread
Results 1 to 15 of 15

minus numbers causing a problem

  1. #1
    kevhatch
    Guest

    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. #2
    Bill Kuunders
    Guest

    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
    news:37505657-B5C1-4659-AA26-7AD934AB3C7B@microsoft.com...
    >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. #3
    kevhatch
    Guest

    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
    > news:37505657-B5C1-4659-AA26-7AD934AB3C7B@microsoft.com...
    > >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. #4
    kevhatch
    Guest

    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
    > > news:37505657-B5C1-4659-AA26-7AD934AB3C7B@microsoft.com...
    > > >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. #5
    Max
    Guest

    Re: minus numbers causing a problem

    > ... 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 <at>yahoo<dot>com
    ----



  6. #6
    Max
    Guest

    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. #7
    kevhatch
    Guest

    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. #8
    Bill Kuunders
    Guest

    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
    >> added day by day?

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




  9. #9
    Bill Kuunders
    Guest

    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. #10
    Max
    Guest

    Re: minus numbers causing a problem

    "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,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
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1 22' N 103 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  11. #11
    Max
    Guest

    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. #12
    Max
    Guest

    Re: minus numbers causing a problem

    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 <at>yahoo<dot>com
    ----



  13. #13
    kevhatch
    Guest

    Re: minus numbers causing a problem



    "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 <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. #14
    Max
    Guest

    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. #15
    Max
    Guest

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



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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