Dear Community,

First, I’m rather new to Excel and have no programming background.  Maybe I’ve undertaken more than I can handle, but I’m a quick learner.  I’ll keep this a short as possible.

I’m retired and volunteered to measure golf courses via GIS/GNSS satellite data collection (latitudes/longitudes/elevations in decimal degrees).  The post-processed field data is given in .csv format in an Excel spreadsheet.

To facilitate building a final Field Measurement sheet, I’ve taught myself many Excel procedures to transfer this data (e.g. how to catenate columns, employ VLOOKUP to accurately populate the correct cells, etc.)

The very last step before I start to populate the “Golf Measure Form” is to satisfy the various distance scenarios encountered in the field.  Each Pivot Point (e.g. PP1) has an offset distance from the true “as the crow flies” Tee to Green Center (GC) distance.  The scenarios are as follows:

• Tee to GC - Straight point to point distance.
• Tee to PP1 to GC - Three points, two distances greater than 1)
• Tee to PP1 to PP2 to GC - Four points, three distances greater than 2)
• Tee to PP1 to PP2 to PP3 to GC - Five points, four distances greater than 3)

It is not as simple as an =SUM solution because of the PP’s given restraints.  The summation MUST match the proper scenario 1, 2, 3 or 4. Any given golf hole, we do not know which scenario will be encountered.  I have tried various =IF strings and other Excel formula solutions to capture the progressive logic without success.

Dear Community,

First, I’m rather new to Excel and have no programming background.  Maybe I’ve undertaken more than I can handle, but I’m a quick learner.  I’ll keep this a short as possible.

I’m retired and volunteered to measure golf courses via GIS/GNSS satellite data collection (latitudes/longitudes/elevations in decimal degrees).  The post-processed field data is given in .csv format in an Excel spreadsheet.

To facilitate building a final Field Measurement sheet, I’ve taught myself many Excel procedures to transfer this data (e.g. how to catenate columns, employ VLOOKUP to accurately populate the correct cells, etc.)

The very last step before I start to populate the “Golf Measure Form” is to satisfy the various distance scenarios encountered in the field.  Each Pivot Point (e.g. PP1) has an offset distance from the true “as the crow flies” Tee to Green Center (GC) distance.  The scenarios are as follows:

• Tee to GC - Straight point to point distance.
• Tee to PP1 to GC - Three points, two distances greater than 1)
• Tee to PP1 to PP2 to GC - Four points, three distances greater than 2)
• Tee to PP1 to PP2 to PP3 to GC - Five points, four distances greater than 3)

It is not as simple as an =SUM solution because of the PP’s given restraints.  The summation MUST match the proper scenario 1, 2, 3 or 4. Any given golf hole, we do not know which scenario will be encountered.  I have tried various =IF strings and other Excel formula solutions to capture the progressive logic without success.

ProTee  IF I understand you correctly I think you just want to use MAX() function.  Also the nested IF() statement has both logic and cell reference errors (OR I don't understand what your criteria are).  But it seems to me you want the distance using 5 points only if that is > using 4 points and 4 points only if that is > 3 points and 3 points if that is > using just 2 points.  If so use:

=MAX(O58, Q58+O69, Q58+Q69+O73, Q58+Q69+Q73+O77)

mtarler 

You are correct and the =MAX function is more concise and works best when a maximum value is required for an active teeing ground in golf.

Unfortunately there is a drawback to utilizing this function in place of the =IFS function.  At any given golf course, we may encounter different scenarios (e.g. Course #1 has 3 tees, Course #2 has 7 tees, Course #3 has 5 tees, etc.).

Our spreadsheet provides encountering up to 8 teeing grounds, although the normal scenario is between 3-5 teeing grounds.  Both my attachments utilize 5 active teeing grounds (e.g. Black, Blue, White, Green, Yellow).  Tees 6, 7 & 8 are inactive and need to depict a blank or at least a value of 0 so those at headquarters are not confused by the data collected in the field if they see a value (e.g. Hole 9's inactive 6, 7 & 8 are blank as they should be and Hole 10's inactive 6, 7 & 8 erroneously show 140 yards).

My web investigations indicate the =MAX function cannot supply a blank or 0 value.  By necessity, we must use the =IF function as depicted in the Hole 9, =IF Solution screen shot.

I want to thank you for your thought, consideration, alternate solution and reply to my post.  I'm filing the =MAX function in my cerebral library for future use when applicable.  If the =MAX function can be utilized and a blank cell or value of 0 can be depicted, please let me know.

Regards, ProTee

ProTee  May I suggest that you just put the MAX() inside the IF() statement.  As you noted, the MAX() is much cleaner and clearer to read than the multiple IF conditions.  So 

=IF( [tee to hole] = 0, \"\", MAX(...) )

But at this point it is mainly a readability and serviceability issue, not functional so of course use what you are most comfortable with.

Glad your issue has been worked out.

ProTee 

Welcome, to one retiree from another.

First, I admire your enthusiasm and your desire to learn. It's hard, though, I must tell you, even though you've done a yeoman's job of describing the situation and provided a helpful image....a copy of your actual spreadsheet would give us here (including those youngsters who've not yet retired, but may have a better handle on Excel than either you or I)...a bit more to work with than the image file alone.

I did notice the elaborate IF function you'd written in that image....have you tried IFS? It is a bit easier to work with multiple conditions in IFS, provided you have them in the right sequence (and I think the sequence you've got in your verbal description should work). Here's a link to a website that you might appreciate in general (great tutorials on all the different functions), but here I've taken you directly to the description of IFS and how to use it. https://exceljet.net/excel-functions/excel-ifs-function

mathetes 

Just got home after a long day.  So much for being retired.  Mathetes, you are my hero!!!

I had the logic correct, just didn't know enough to use the IFS formula.  It works like a charm.

I've attached a screen shot to depict your solution, IFS.

I am also looking forward to try the MAX solution as proposed by another poster.

Once again, thanks!