User Profile
PeterBartholomew1
Silver Contributor
Joined Sep 26, 2018
User Widgets
Recent Discussions
Re: Calculating and adding time
Based upon OliverScheurich workbook but using 365 formulas. delay = ROUND(distance*correction, 0) startTimeSec = 24*60*60*raceStart + delay# startTime = raceStart + delay#/(24*60*60) intervals = startTime# - DROP(VSTACK(raceStart, startTime#),-1) startNumber = SCAN(1, SIGN(intervals#), SUM)Re: Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
This is a 'prettied up' version of the above that defines a Lambda function for the calculation. Worksheet formula = SUMMARISEλ(name, place) where SUMMARISEλ = LAMBDA(name, place, LET( JOINλ, LAMBDA(x, TEXTJOIN(",", , SORT(x))), header, {"Total points", "Finishes"}, points, INDEX(pointList, place), GROUPBY( name, HSTACK(points, place), VSTACK(HSTACK(SUM, JOINλ), header), 0, 0,-2 ) ) );Re: Data Formatting
To be honest, I am not sure whether this 365 solution will be of any value to you, it uses specially written versions of MAP to overcome the array of array problem in Excel 365. The problem-specific formulas are Worksheet formula = MAPλ(Table1[sfn], Table1[RNTI], Table1[DCI], UNPIVOT.CSVλ) where UNPIVOT.CSVλ = LAMBDA(sfn, RNTI, dci, HSTACK( EXPAND(sfn, COUNTA(TEXTSPLIT(RNTI, , ",")), , sfn), TEXTSPLIT(RNTI, , ","), TEXTSPLIT(dci, , ",") ) ); The catch is the function MAPλ that I published on GitHub is not straightforward, though it may be easy enough to use.Re: unpivot data and handle merged cells without using Power Query (Unpivot_Toolkit)
Looks impressive! A useful step towards providing new calculation options. My first choice might be to use array formulas directly on the data objects held within your crosstab The next might be to use your 'souped up' unpivot followed by database-style aggregations Repivot to form a new crosstab with the restructured arrays for calculation Choice is good.Re: Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
Just to demonstrate it is always possible to make a solution more complicated! Worksheet formula = RANKEDλ(Name, Finishes) // RANKEDλ calls SCOREλ by player then sorts results descending RANKEDλ = LAMBDA(name, finishes, LET( pts, MAP(finishes, SCOREλ), SORTBY(CONCATENATE(name, ": ", pts), pts,-1) ) ); // SCOREλ converts finishes into a point score SCOREλ = LAMBDA(fin, LET( places, DROP(REGEXEXTRACT(fin, "\d+", 1),,-1), SUM(INDEX(points, VALUE(places))) ) );Re: Weekday only calendar
= MAPλ(months, CALENDARλ) CALENDARλ = LAMBDA(monthIdx, LET( monthStart, DATE(year, monthIdx, 1), mGrid, SEQUENCE(6,7,FLOOR(monthStart,7)), cleanMonth, IF(MONTH(mGrid)=MONTH(monthStart), DAY(mGrid), ""), monthTxt, TEXT(monthStart, "MMM"), weekdays, {"Mon","Tue","Wed","Thu","Fri"}, days, EXPAND(DROP(cleanMonth,,2),6,6,""), VSTACK(monthTxt, weekdays, days) ) ); This is based upon the m_tarler solution. I set out to conform to the approach of only one formula per sheet so wanted the year calendar as one formula.Re: Formual Error
This reply was based upon a copy of the IlirU dataset and generates the result crosstab shown in yellow. = LET( TOP3λ, LAMBDA(s,t,IF(@s>=LARGE(t,3), @s, 0)), crosstab, PIVOTBY(Attendance, DATEVALUE("1/"&Month), Point, TOP3λ,,0,,0), nonblank, BYROW(DROP(crosstab, , 1), OR), FILTER(crosstab, nonblank) ) The Lambda function TOP3λ returns a scalar '@s' if it is one of the top 3 values within the overall list of points 't' for the month, 0 otherwise. Blank rows are then filtered out.Re: Loss carry forward for limited years
Patrick2788 "Typical arrangement" ... I doubt it! I have attached a file that contains a possible layout for a single product line. The file was used to support an on-line presentation so it is more focussed upon demonstrating solution techniques than attempting the most efficient solution for each problem. The message being that a dynamic array solution in Excel may have very little in common with the traditional spreadsheet!Re: What is the SERIES function?
The final parameter is simply a sequence number defining the order in which the series are stacked on the chart. The user experience offered by the SERIES command is somewhat different from other functions because it is not processed by the Excel calc engine but, rather, by a distinct charting engine (this is my understanding, not official documentation I have at hand). Change the data series in a chart - Microsoft Support
