Search results

Why doesn't it surprise me that Lorimer managed to come up with amazing solutions! Now, 10 years on and with an entirely new calculation engine, one could have = MAP(listIMEI, Checkdigitλ) where Checkdigitλ = LET( digits, EXPLODEλ(IMEI), LuhnDigit, VALUE(TAKE(digits, -1))...

How things change! A couple of years or so ago, one might have had = LET( m, ROWS(array), n, COLUMNS(array), k, SEQUENCE(m*n,,0), r, 1+QUOTIENT(+k,n), c, 1+MOD(k, n), INDEX(array, r, c) ) Now, with array shaping functions, = TOCOL(array) will do the job! What has...

Hi Pascal It appears that I have simply matched you step by step = LET( filtered, FILTER(data, timeline>=startDate), accumulated, SCAN(0, TAKE(filtered,,-1), SUM), filteredDate, TAKE(filtered,,1), XLOOKUP(target, accumulated, filteredDate,,1) ) There is a slight difference...

Hi Pascal, You could make it a lifetime mission to bring all the old solutions up to date! I suspect that reviewing the 'Vault' would throw up some interesting challenges. In the present case, my rework would be simpler, but less robust, than yours. = AVERAGE(TAKE(SORT(data), 8)) I think the...

I love the typo in the caption! Non-edible? I guess if you ate it no one is going to try to make edits :DD Oh well, these things happen.

FILTER, followed by TEXTJOIN would come to mind.

I am not sure I have understood this correctly but, if you no longer care about the "R6" code then you could simply list all the work instruction codes. = LET( record, Table1[Constructive_Feedback], k, SEQUENCE(1, 12), WINum, LEFT(TEXTAFTER(record, "TSRC-PROD-",k), 6)...

This is strictly for 365 (as is all my work). To place the results in the table, you will need to concatenate multiple WIs or simple select the first. = LET( followsR6, TEXTAFTER([@[Constructive_Feedback]], "R6",{1,2,3}), rationalle, TEXTBEFORE(followsR6, CHAR(10),,,1), wInstrNum...

Not quite the output format you request but with 2021 you could use the trick of offset defined ranges along with FILTER.

As @p45cal pointed out, it is an Excel formula, albeit one that is written specifically for Excel 365. The formula is to be found in cell K2.

There seems to be something wilfully perverse about the alternating arrangement of the data and associated results! That said = LET( wrapped, WRAPROWS(data, 2), matches, FILTER(TAKE(wrapped,,1), TAKE(wrapped,,-1) = "FON"), TOROW(HSTACK("Result " & SEQUENCE(ROWS(matches)), matches))...

This is another example of Microsoft's 'array of array' foul-up! It is unusual for this type of problem to arise in the context of a single (scalar) text value. It is far more usually applied to a list (maybe a few thousand terms). Yet = TEXTSPLIT(list, "-") fails to return the correct result...

Broadly speaking = BYROW(priceVariation, LAMBDA(variation, TEXTJOIN(" | ",,SORTBY(day, variation,-1)) )) will return the result you require.

It is possible to replicate the appearance of the chart using and area chart in combination with error bars on a scatter chart and text boxes but that is not to say that the original chart was created using Excel. It is more of an infographic than a quantative chart. I used an exponential...

Nice! Well spotted on the wrapping problem. My attempt to create one condition was something of a nuclear option when simply used to drive a conditional format. = TestPriorCellsλ(count, string) =OR( MAP( SEQUENCE(1, n, 0, -1), LAMBDA(k, LET( testCell...

if the two selected reasons were non-adjacent you could pick the contents up as an array by using = SUM(SUMIFS(Duration, Reason, CHOOSE({1,2}, selection1, selection2))) With 365 I would be more inclined to use = SUM(SUMIFS(Duration, Reason, VSTACK(selection1, selection2))) (Note: Every formula I...

No. The 'table' is simply a defined name applied to the range $T$2:$V$24. To my mind, using Ctrl/T to convert the range to an Excel Table would have improved the formula by making it respond dynamically to new data. However is is better for you as it stands! You could even revert to the...

I guess Il 'foglio' was the clue :cool: Let me know how the formula translates.

Just playing! I used a Lambda function to determine whether a cell is to be highlighted or not. LatestEntryλ is defined to be = LAMBDA(cell, ISTEXT(cell XLOOKUP(cell, list, list, , , -1)) ) That is, look up the current cell in the list and return the last match as a range reference...

I assume you are using a legacy version of Excel. Otherwise you could use FILTER.

@hofiy I suggest that this should be a new thread and it would be up to you to define the theoretical basis of any calculation you wish to perform.

As with all my formulas, this appears to use defined names and not cell references to return data. Are the names defined in the new workbook and do they refer to the correct cells?

I do not understand the question. If you use a lookup formula (though VLOOKUP is obsolete) to look a value up in an array it will produce the same result every time. = XLOOKUP(value, number, parent) Given you have multiple occurrences, it is also possible to use FILTER (using Excel 365 or 2021)...

Something different! = LET( Addλ, LAMBDA(x,y, IF(ISNUMBER(y), x+y)), data, HSTACK(amount, reset), accum, SCAN(0, TOCOL(data,1), Addλ), compact, FILTER(accum, accum), IF(reset="Y", compact, "") )