# Thread: There has to be a better way

1. ## There has to be a better way

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.  Reply With Quote

2. 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)?  Reply With Quote

3. 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.  Reply With Quote

4. 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))  Reply With Quote

5. 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  Reply With Quote

6. 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.  Reply With Quote

#### Posting Permissions

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