User Profile
PeterBartholomew1
Silver Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: File Attachments
The problem persists! The workbook appears to upload but does not appear in the final posted version. Code windows and pictures get one so far but, since I only use dynamic array solutions, the OP tends to give up if called to implement a solution. The other problem I have encountered is responding to a post that shows 0 replies, only to find there are several once I have posted. It would be good to get the issues sorted!Re: How to distribute the value of one cell evenly in a sequence for the value of another cell?
An approach I would suggest is to distribute the change uniformly between rows 0 and 21 (ensuring that the region outside the reduction zone has the exact number of stiches specified). Rounding the fractional parts to the nearest integer will give the number of stitches required for each row. Differencing the count of stitch will return increase/decrease for each row. I use 365 so the formulas may look unfamiliar but it should convey the idea. = LET( DIFFλ, LAMBDA(value, value - DROP(VSTACK(0, value),-1)), rowNum, SEQUENCE(1+Rows,,0), stiches, ROUND(rowNum * Change / Rows, 0), table, DROP(HSTACK(rowNum, initial+stiches, DIFFλ(stiches)),1), VSTACK(table, HSTACK("", "Total change", SUM(TAKE(table,,-1)))) )Re: The Diagonal Suite: Gentle thunking goes a long way!
I think your use of thunks may well be closer to the original intent than mine. Although you define the code to generate multiple arrays, you only evaluate the ones selected by the use case. I think such 'code insertion' was central to the concept of thunks (any functional programmers out there might like to comment). I have used thunks in a rather different manner. I actually use a LET variable to force the evaluation of the content before forming the thunk. My mindset is more one on "I may reference this data many times and wish to ensure that the processing is only performed once". I am, in effect, passing data by reference and avoid the prospect of disappearing down the rabbit hole performing recursive calculation as I go! I find it very difficult to determine which strategy should perform the better and suspect it is case-specific. The examples of anti-diagonal (weighted) summations I quoted, fit into a pattern of discrete convolutions. Ultimately one can resort to Fast Fourier Transforms but that is far removed from the benefits of thunking you set out to convey! BTW, I really enjoy your coding!Re: Unwanted automatic change of text number format to scientific
You will find a setting under Options/Data/Automatic Data Conversion which sets "Convert digits surrounding the letter "E" to a number in scientific notation" In response to the old comment "They need to disable this dumb "feature". I can't imagine a single person on the planet that finds it useful", It is not a dumb feature because anyone involved with scientific work or engineering is likely to require such notation for almost every number in the workbook. 1.24E+12 is a standard way of representing the number 1237650000000 to 3 significant figures. To ask otherwise is the same as expecting a leading "-" to be treated as a hyphen followed by text. I does happen, but Excel is really about numbers and calculation.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) )
