Results 1 to 6 of 6

Thread: There has to be a better way

  1. #1
    Seeker EchoDelta's Avatar
    Join Date
    Feb 2015
    Posts
    8
    Articles
    0
    Excel Version
    2013

    There has to be a better way



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have written formulas to dot boxes in Golf Score Cards based on individual handicaps. Formulas are in Rows 25 through 87 and Columns C through U. Surely there is a better way to do this task.
    Thanks in advance for any suggestions.
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Better in what way, what don't you like (I don't like the layout at all, but then I don't like golf so that is probably immaterial)?

  3. #3
    Seeker EchoDelta's Avatar
    Join Date
    Feb 2015
    Posts
    8
    Articles
    0
    Excel Version
    2013
    Thanks for your reply. Not sure what you do not like about the layout (or what you mean exactly about the layout). The formulas were all written prior to the increased number of arguments permitted so I had to make more cells dedicated to the formulas. I would like to make the formulas shorter but not sure if it can be done. Thanks again.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    I don't understand what you mean by ... written prior to the increased number of arguments permitted.

    As to the layout, I much prefer to capture the data in a list format, one that the formulas are consistent (not like yours that cannot be copied down line by line), and then show the results on a separate sheet.

    You can easily shortten the formulae. For instance, take the formula

    =IF(C$12>39,($W$30),IF(C$12>38,($W$30),IF($C$12>37,($W$30),IF(C$12>36,($W$30),IF(C$12>35,($W$30),IF(C$12>34,($W$29),IF(C$12<35,C26,0)))))))

    you test for <39, then for >38 then > 37 and so on. >34 includes 35, 36, 37 and so on, so

    =IF(C$12>34,W$30,IF(C$12<35,C26,0))

  5. #5
    Seeker EchoDelta's Avatar
    Join Date
    Feb 2015
    Posts
    8
    Articles
    0
    Excel Version
    2013
    When I was originally learning Excel, and wrote these formulas, I was using Office 95, and there was a limit of seven “IF” statements per formula. Therefore I used seven formulas to calculate one cell, as I needed to refer to an additional formula when I reached the maximum of seven. Following your advice and now using Office 2007, I have been able to shortened the formulas immensely.

    Resulting formula is: =IF(C$12>35,($W$36),IF(C$12>17,($W$35),IF(C$12>0,($W34),IF(C$12=0,($W34),IF(C$12<0,"")))))
    I am not even sure that this cannot be improved upon.

    This is what I meant when I was saying there has to be a better way.

    I really do appreciate you teaching an old dog new tricks.

    Thanks again for your time and effort.

    Ed

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    You certainly can improve upon it.

    You do not need the brackets around cell references, $W$36 instead of ($W$36)
    You don't need to test separately for >0 and =0, IF(C$12>0,($W34),IF(C$12=0,($W34), you can do it in one step, IF(C$12>=0,$W34
    If you test for the numbers to finally get to >=0, any other result must be less than 0, IF(C$12>0,($W34),IF(C$12=0,($W34),IF(C$12<0,"", so no need to test, it defaults like so IF(C$12>=0,""

    In summary, all you need is

    =IF(C$12>35,$W$36,IF(C$12>17,$W$35,IF(C$12>=0,$W$34,"")))

    BTW, although Office 95 only allow 7 nested statements, this final formula would work then as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •