skip nav

Forums

Nerdy thread

Sun, May 12 2019 2:02 AM by ScottHope. 64 replies.
  • ScottHope United Kingdom
    5,905 Posts
    Sat, May 11 2019 1:57 AM

    Does anybody fancy having a go at trying to turn this into an Excel formula / spreadsheet?

    I did it with nested IFs and AND functions, but it's about a mile long and it makes my head hurt just to look at it, there must be a better way, anyone?

    =IF(Lie_percent<=20,IF(Distance+Elevation>35,IF(Elevation>6,3,4),IF(Elevation>5,5,4)),IF(Lie_percent<=40,IF(Distance+Elevation<20,13,IF(AND(Distance+Elevation>=20,Distance+Elevation<30),12,IF(AND(Distance+Elevation>=30,Distance+Elevation<40),11,IF(AND(Distance+Elevation>=40,Distance+Elevation<=45),10,IF(Distance+Elevation>45,9,""))))),IF(Distance+Elevation<30,16,IF(AND(Distance+Elevation>=30,Distance+Elevation<40),17,IF(AND(Distance+Elevation>=40,Distance+Elevation<50),18,19)))))+Distance+Elevation

    Digg
    Delicious
  • crmamx United States
    260 Posts
    Sat, May 11 2019 9:11 AM

    ScottHope:

    Does anybody fancy having a go at trying to turn this into an Excel formula / spreadsheet?

    I did it with nested IFs and AND functions, but it's about a mile long and it makes my head hurt just to look at it, there must be a better way, anyone?

    =IF(Lie_percent<=20,IF(DistElev>35,IF(Elevation>6,3,4),IF(Elevation>5,5,4)),IF(Lie_percent<=40,IF(DistElev<20,13,IF(AND(DistElev>=20,DistElev<30),12,IF(AND(DistElev>=30,DistElev<40),11,IF(AND(DistElev>=40,DistElev<=45),10,IF(DistElev>45,9,""))))),IF(DistElev<30,16,IF(AND(DistElev>=30,DistElev<40),17,IF(AND(DistElev>=40,DistElev<50),18,19)))))+DistElev

    Not the answer you want but I like what I see and am going to look at programming it into Excel, one small baby step at a time.

    Your shot distance calculation formula you did for me was also a mile long and used functions I did not even recognize So rather than one formula, I went back and programmed one step at a time. It's isn't very professional looking but it does the job and I understand it. Also it eliminates me pestering you with every little detail of what I am trying to do....LOL

    Does this guy have any more of these charts or formulas? I would love to see them.

    Curtis

     

    Digg
    Delicious
  • ScottHope United Kingdom
    5,905 Posts
    Sat, May 11 2019 1:41 PM

    Hi Curtis, I shall be interested to see what you come up with for that bunker chart my friend. My brain let out a sigh when I'd finished working on my formula.

    Understanding formulas is paramount for moving forward with Excel, and whether they are your own or someone else's creation, you are doing the absolute correct thing in making it understandable for you. If it doesn't make sense to you, you are not going to progress with it.

    The guy who came up with those bunker formulas is Puttluck. I looked at his profile today and he has not done anything recently, and as far as I'm aware, he has no other tips available.

    P.S. He didn't actually make that chart though, he originally posted his formulas in the forum here, but they were a bit difficult to understand, so I thought I would try to simplify them and came up with the chart (flow chart?)

    Digg
    Delicious
  • DoctorLarry United States
    2,174 Posts
    Sat, May 11 2019 5:01 PM

    Yes - it is hard to create so much logic in a single formula so I also break it down into steps - particularly for initial clarity and if I decide to change some parameters later.  Often I find doing 2 or 3 separate logical ifs and setting other parameters lets me put together the final simplified one.

    Digg
    Delicious
  • ScottHope United Kingdom
    5,905 Posts
    Sun, May 12 2019 2:02 AM

    You're right Larry, so many parentheses brackets to pair up correctly is very confusing.

    I might have another look at it when I've recovered.  8 )

    Digg
    Delicious
RSS