User Profile
PeterBartholomew1
Silver Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: How to convert dates in excel to common format.
The created on field appears to contain text. It is human-readable but of no meaning for Excel. Another possibility is to leave the text on the worksheet but to use the date in a formula you could start by converting it to a date = LET( createdDate, DATEVALUE(SUBSTITUTE(textDate,".","/")), createdDate ) or, if you consider that shows too much irrelevant detail on the worksheet you could define a trivial Lambda function = DATEVALUEλ(textDate) where DATEVALUEλ = LAMBDA(textDate, LET( createdDate, DATEVALUE(SUBSTITUTE(textDate,".","/")), createdDate ) );Re: What was the moment you realized Excel was more powerful than you thought?
There have been no moments of blinding revelation for me but when LAMBDA appeared, with its claims of Turing completeness, I set out to test the functionality by implementing some of the more complex algorithms that might come to mind. I first implemented a 4th order Runge-Kutta algorithm for integrating ordinary differential equations and then went on to implement a Fast Fourier Transform algorithm. The tasks weren't easy and it still might be better to use a Python library routine, but the fact that it proved possible made be realise that we are now dealing with a serious computing environment.Re: Wider Excel discussion
Sometimes defining and naming your own styles is better. I once had a workbook crash because the VBA referenced default styles. The default names all changed when the workbook was used in Japan but my code didn't update. It was only the custom names and formats that went through without error.Re: Wider Excel discussion
I suspect that the Styles functionality within Excel is grossly underused. The actual default values chosen by Microsoft are ridiculous but the basis for a better structured use of formatting is there. To be able to invoke the formatting by formula as an alternative to the GUI would open up new possibilities for the 'one formula sheet'.Re: ATAN(), ATAN2() return same value?
As SergeiBaklan pointed out, you have omitted as set of parentheses in the division. However, care needs to be taken because ATAN loses sign information when the division is carried out and only returns angles in the range -π/2 to +π/2. ATAN2 retains information on the sign of each parameter and so can return angles in the range 0 to 2π. The angles returned by the two functions may differ by π. In the present case, I believe entering a value of 32.176 in cell $C$3 will still make the corrected formulas give different results because it results in a sign change.Re: Best way to use maxifs combined with replacing values in "lookup array"
You could very likely us an Excel formula. It lacks Python's extensive libraries but, other that that, it is perfectly capable of carrying out calculation. = LET( modifiedArr, IF(lookupArr<0.1, replacementArr, lookupArr), conditionalMax, MAX(IF(criterionArr, modifiedArr)), conditionalMax )Re: Wider Excel discussion
Hi David, Craig Hatmaker is including some thunks in his BXL 5g modules. I am getting comfortable with using thunks to reference arrays that I want to access within a calculation. If I am not allowed an array of ranges/arrays, sobeit; I will use an array of functions. What I would like some support on is getting Microsoft to implement a built-in function that evaluates and stacks an array of thunks as EVALTHUNKARRλ does. Our function is full of tricks and works pretty well but I can't help thinking that it would perform better and be more acceptable to the user community as a built-in function. On an entirely different track, something else I would like is = VSTACK( STYLE(result, "Output"), STYLE(BYCOL(result, SUM), "Total") ) which could give nicely formatted output without resorting to the nightmare world of conditional formatting!Re: Using TEXTSPLIT without copy-fill-down
The array of arrays should not be a problem; it is simply the typical output from array calculation. Since the original date of this post, I have written helper functions (with a bit of tidying up from djclements ) that return arrays of arrays. = MAPλ(values, LAMBDA(values, TEXTSPLIT(values, ";"))) or by Currying the LAMBDA function Worksheet formula = MAPλ(values, TEXTSPLITλ(";")); where TEXTSPLITλ = LAMBDA(s, LAMBDA(v, TEXTSPLIT(v, s))); The function MAPλ is to be found at A version of Excel MAP helper function that will return an array of arraysRe: Splitting Array Elements into Multiple Elements
I do not fully understand either the data or the required output but the attached file contains some helper functions that I published which address Microsoft's array of array shortcomings. = MAPλ(GPO[about:config setting equivalent], LAMBDA(element, TEXTSPLIT(element, ";")) ) https://gist.github.com/pbartxl/a14b250985da31be843ce9ff35d888fcRe: Dynamic Calendar in Excel 365
I tried part of the problem as a challenge; namely creating a blank calendar. My approach starts with the idea that the 'natural' way for Excel to store references to an array is as a thunk. I set out first to create a single month entry CALENDARMONTHλ = LAMBDA(SoM, LET( calendarMonth, 1+SEQUENCE(6,7,FLOOR.MATH(SoM, 7)), mm, MONTH(SoM), VSTACK( EXPAND(TEXT(SoM, {"mmm","yyyy"}),,7,""), {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, IF(MONTH(calendarMonth)=mm, calendarMonth, "") ) ) ); Then the worksheet formula is = LET( ym, DATE(yyyy, SEQUENCE(8,3,mm), 1), MAPλ(ym, CALENDARMONTHλ) ) That rather begs the question of MAPλ. I had ideas to portion the entries out as monthly lists represented by an array of thunks, but didn't get around to that.Re: Add Letter Into Number
Adding a letter to an existing cell is not allowed in a functional programming language such as the Excel formula language. Conditional formatting changes the way in which a number is displayed without changing the underlying number. Other than that, you will need an imperative programming language such as VBA. A Worksheet Change Event would be able to read column D and replace it with something else because state changes are an integral part of such languages.
