Forums

Nerdy thread

• ScottHope 10,440 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

• crmamx 369 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

   

• ScottHope 10,440 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?)

• DoctorLarry 4,311 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.

• ScottHope 10,440 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 )

• ScottHope 10,440 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.  ; )

• DoctorLarry 4,311 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?  \;-)

• ScottHope 10,440 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.  : )

• ScottHope 10,440 Posts

  Wed, Sep 18 2019 6:42 AM

  Okay, here's my answer to this⇧...

  

  The SMALL function in the top box, finds the smallest value in the array B3:B12. The 1 after the array reference in the formula tells the small function to return the 1st smallest value in the array to the cell. Put a 2 there, and the formula would return the second smallest value in the array etc.

  The SMALL function in the middle box includes an array of values {1,2,3}. This instructs the small function to return the 1st, 2nd and 3rd smallest values from the B3:B12 array. If you try this formula though, it will only return one value to the cell, because a formula (AFAIK) can only return one value to a cell, and in this case it will be the smallest value in the array B3:B12, because the first number in the array {1,2,3} in the formula, is 1.

  The AVERAGE function in the bottom box will give us the result we are looking for. It returns to the cell the average of the values returned by the SMALL function, if that small function has an array of values to search for.

  ---

  If you need to use larger arrays, because nobody in their right mind would want to type out a huge array {1,2,3,,,100} into a formula, then you can do that by adding the ROW and INDIRECT functions into the above formula. But that's for another day.

  Anyone still awake? Lol.  8 )

• craigswan 31,819 Posts

  Wed, Sep 18 2019 6:46 AM