Posts by SansSuit

Thanks for your help. I appreciate it.

It's on the way!

10% sent to [email protected]

I am an avid skydiver. I have been jumping for 28 years and have 4500+ skydives. Parallel to my passion of exiting aircraft in flight, is my passion for Excel and my Excel logbook. The workbook started out as a very simple 8 column spreadsheet that I created in ClarisWorks around 1996. As my participation in the sport increased and my knowledge of spreadsheets grew, it has evolved into a workbook with numerous worksheets.

90% of the workbook exists simply because I figured out a way to make Excel display some result. I’m constantly searching for ways to improve it, add formulas, get new results, make things simpler, etc. I've taken a few online Excel courses. I am currently unemployed (retired, looking for a hobby job), so my search for Excel knowledge is exclusively focused on improving my logbook. Simplified, Excel purely for the love of Excel!

I did not attach the workbook because I don't want to make it public to the world. To turn it into a sample file would be too much effort. I will be happy to email the file to anyone who wants to see it, whether or not you are the one hired. The workbook is 1.3 MB and for each jump there are 12-15 input columns. Most of the workbook is results of computations of those inputs. There are 8 worksheets (Pared down from 16, the others were not pertinent to the “working” sheets.) but 4 of the sheets are of duplicate nature to 2 others.

Is there someone who would be willing to take a look at it and offer suggestions, critiques, improvements, etc.? I’m not looking for someone to crawl into every cell to find errors, I’m just looking to improve what I have. Judging by the expertise level at OzGrid, my workbook is relatively simple and straightforward. There are no charts, pivot tables, or VBA. This is not an urgent issue. I would think a week would be a good time frame for someone to look at it at their leisure.

Cautionary note: Some of my skydives have been made without the benefit of a jumpsuit or clothes of any sort. Nekked skydiving is legit and is included in a fair portion of the workbook.

Thanks!

Quote from Fluff13

Ok, how about

=LET(w,WORKDAY.INTL(A3-1,SEQUENCE(NETWORKDAYS.INTL(A3,MAX(A3:A100),"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

This will now work if the first date is a Friday, which it would ignore before.

Beautiful! My workbooks are now 100% perfect. There is absolutely zero that can be improved upon.

Thanks again!

Quote from Fluff13

How about

=LET(w,WORKDAY.INTL(A3,SEQUENCE(NETWORKDAYS.INTL(A3,A69,"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

I am totally gobsmacked! I threw this problem out there believing that there could not be a solution. You nailed it! I thank you very much.

Is it possible to tweak this most excellent formula a bit to include blank cells at the end to allow for future dates to be added? When I change A69 to A70, it gives me an error. I'm thrilled with the formula the way it is, but if it could include blank cells at the end it would be perfect. I realize now that I should have stated this in the original post. My bad.

Again, I am most appreciative of your help!

Undoubtedly, this particular problem is the absolute LOWEST priority of any calculations I need. I worked out an answer but it is again clunky and takes up a bunch of real estate.

In a list of dates, I am looking for the max number of occurrences in any one weekend, defined as Fri., Sat., and Sun., and the dates of that weekend. The data contains duplicates but will always be chronological.

In my clunky solution, I listed all of the possible weekends and then counted occurrences in each weekend. Is there a less caveman-like way of obtaining the same results? Ideally, I would like to eliminate columns C:E. Adding a helper column or two to the DATA would be acceptable.

I would appreciate any help but please don’t lose any sleep, it isn’t all that important.

Thanks!

weekends.xlsx

Quote from Fluff13

How about

=LET(a,TEXT(SEQUENCE(366,,DATE(2020,1,1)),"m/d"),FILTER(a,ISNA(MATCH(a,TEXT(dateN,"m/d"),0))))

Obviously, Excel ninja magic does exist because I asked for some and that is exactly what I received. Thank you very much!

Greetings-

I am looking for a simpler solution to listing the month/day combinations that are not present in a long list of dates. In this example there are 900 dates (dataN); they are in order and there are many duplicates. For instance, the first result is January 4, because there is no January 4 in any of the dataN dates. I need to list these month/days.

I did work out a solution but I’m not convinced that it is the best or simplest. Column C is a complete list of possible month/days. I used 2020, the last leap year. Column D compares the month/day against dataN. If there are any matches, it returns nothing. If there are no matches, it returns that date. Column F is a unique list of the positive results in Column D. The years are totally irrelevant.

I would appreciate any Excel ninja magic you all can come up with.

dates.xlsx