User Profile
PeterBartholomew1
Silver Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Double Thunking Works Wonders!
Wow! This was quite a discussion. Having initiated it, I then sometimes had problems keeping up! I gave a presentation "Excel - a Replacement for the Spreadsheet" for Danielle Stein Fairhurst earlier in the week and had some concerns about pitching programming techniques to Excel users. This thread makes my work look mundane 🤩Re: Best way to get rolling 12-month totals
Hi John This site seems to be getting flaky! I can't always post and sometimes attached files get discarded. I also preferred the days when the entire discussion was laid out chronologically. At least @ is showing parties to the current discussion today. See whether my 12 Month Total file appears this time around.Re: Best way to get rolling 12-month totals
The OP seems to want to work with pivot tables so that counts me out! It all depends what 'simple' means to you. Is a sea of simplicity with thousands of simple grade school sums really simple overall? I think that a single array can be simpler to follow despite its more abstract nature. Perhaps not in this case though?Re: SUMIF help
To pick up from mathetes​ post, it is probably easiest not to use a Pivot Table in the first place. If that is what you have you will need a list of distinct items then use GETPIVOTDATA to extract values from each. distinctItems = SORT(UNIQUE(VSTACK(Table1[Item], Table2[Item]))) = IFERROR(GETPIVOTDATA("Amount", pivotTable1,"Item", distinctItems), 0) + IFERROR(GETPIVOTDATA("Amount", pivotTable2,"Item", distinctItems), 0) If, on the other hand, you have access to the source data then you could stack the data first then pivot it = LET( stackedItems, VSTACK(Table1[Item], Table2[Item]), stackedAmount, VSTACK(Table1[Amount], Table2[Amount]), GROUPBY(stackedItems, stackedAmount, SUM) )Re: SEQUENCE formula with curly brackets
As an alternative to setting both row and column indices in the INDEX function, you could use the '@' operator to truncate any array output to the value of its initial (scalar) element. = LET( y, SEQUENCE(ROWS(x)), z, FILTER(y, x=""), str, @INDEX(z, k), rws, @INDEX(z, k+1)-str, return, SEQUENCE(rws,1, str, 1), return ) [note: I have used defined names for 'x' and 'k']Re: Profit/Loss per Day Calendar
That is because the formula specified a specific range that matches the data. I have modified HansVogelaar​'s workbook to use a table for the profit/loss data that should resize as data is appended. The names are defined in terms of table structured references. The missing attachment:Re: Looking up specific period financials from multiple period worksheet
If you named the header row containing the month names on your second sheet and and the data itself you could use XMATCH to determine the starting column number you require from your data and use that to drop data from earlier months before selecting the data you require. = LET( colNum, XMATCH(month, monthHdr), TAKE(DROP(monthlyData, , colNum-1), , 3) ) The formula returns a range reference so functions like TRIMRANGE can be used to capture data from tables of varying length.Re: calculating years between 2 dates help
DATEDIF would return a #NAME? error if you actually typed the function as "dateif". = DATEDIF(startDate, endDate, "Y") should work. The problem with = YEARFRAC(startDate, endDate) is that you are formatting the count of years as a date. If you are in the US, 1/3/00 is the 3rd January and General format will show it as 3. Anywhere else in the world the date is the 1st March which has a date value of 61.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.
