skip nav

Forums

Re: HTML issue.

Fri, Sep 13 2019 1:23 AM by ScottHope. 67 replies.
  • ScottHope United Kingdom
    5,974 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
    264 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,974 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,350 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,974 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
  • ScottHope United Kingdom
    5,974 Posts
    Thu, Sep 12 2019 9:33 AM

    Excel Question

    I can do this, but just out of interest I'm looking for alternative ways, because I don't fully understand how the way I do it works, and that irks me a little, lol.

    It's connected to WGTs members average calculation.

    Take a bunch of numbers, say 30 values in a column...
    find the lowest, say 10 of those...
    then calculate the average of those 10.

    Sorting of the column of numbers is not permitted.

    Thank you for any and all responses.  ; )

    Digg
    Delicious
  • DoctorLarry United States
    2,350 Posts
    Thu, Sep 12 2019 11:05 PM

    ScottHope:

    Sorting of the column of numbers is not permitted.

    Thank you for any and all responses.  ; )

    Are you trying to do this in one cell?

    can I write a macro?  \;-)

    Digg
    Delicious
  • ScottHope United Kingdom
    5,974 Posts
    Fri, Sep 13 2019 1:23 AM

    I can do it in a single cell, yes.

    I have an oldish version of Excel (2013), so if you have something more up to date, you might be able to use one of the recently introduced dynamic array formulas.

    You can show me your macro Larry if you like, but a single cell formula is the goal.  : )

    Digg
    Delicious
RSS