User Profile
PeterBartholomew1
Silver Contributor
Joined Sep 26, 2018
User Widgets
Recent Discussions
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 SupportExcel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data arrayRe: Random Contractor Selection List
I have attempted to use a pseudo-random number generator that I had on file from Lori Miller. The advantage of this is that the values are not volatile so it is possible to add contracts without reassigning existing tender invitations. My worksheet formula is = INVITEDλ(contractorLst, COUNTA(Contracts)) where INVITEDλ = LAMBDA(contractors,n, LET( rand, WRAPROWS(PseudoRandλ(2*n, seed), 2), m, COUNTA(contractors), selection1, 1 + INT( m * CHOOSECOLS(rand, 1)), selection2, 1 + INT((m-1) * CHOOSECOLS(rand, 2)), selections, HSTACK(selection1, selection2 + (selection2>=selection1)), invited, INDEX(contractorLst, selections), invited ) ); The random number generator derived from Lori is PseudoRandλ = LAMBDA(length, [seed0], // Written by Lori Miller LET( seed, IF(ISOMITTED(seed0), 123456789, seed0), case, SEQUENCE(length, , , 0) * {13, -17, 5}, rand, SCAN(seed, case, LAMBDA(s, i, BITXOR(s, BITAND(BITLSHIFT(s, i), 2 ^ 32 - 1)))), TAKE(rand,,-1) / (2^32) ) );Re: Index & Match Formula Not Working
Maybe it was just the 0 missing from the MATCH to required exact matches. With 365 I would use array formulas and truncate entire column references to conform to the actual data. = INDEX(returnArray, XMATCH(TRUE, BYROW(lookupArrays=lookupValues, AND))) or = XLOOKUP(TRUE, BYROW(lookupArrays=lookupValues, AND), returnArray)Re: Excel charts: revenue and margins over a time period
I think you need to declare the charts with Switch Row/Column to plot against year. I am also not convinced that there is much to be gained by stacking the column and line presentation within a single chart (see below). Two adjacent charts may well be clearer.Re: How to create a multi-tiered percentage-based bar chart?
The essential characteristic of the replies you have received is that the data needs to be unpivoted in order to plot it. This can be done using PowerQuery or by formula. = LET( wrapped, WRAPROWS(TOCOL(data),2)/100, name, TAKE(WRAPROWS(TOCOL(IF(data, Tabelle1[Employee])),2),,1), hdr, TAKE(WRAPROWS(TOCOL(IF(data, header)),2),,1), HSTACK(name, hdr, wrapped) ) If you include the attribute names in the chart data then the presentation can be labelled more meaningfully.Re: Groupby or Filter function
Your question assumes the statistical analysis is complete and you simply need to reformat them. I was just pointing out the alternative option of performing the statistics in a manner that would output the results you require directly (depending upon the raw data format) = GROUPBY(group, value, HSTACK(AVERAGE, MEDIAN, MODE),,0)Re: Automatically convert numbers to time
This is just a slight variation on NikolinoDE 's formula approach. The first step is to allow the 4-digit format you require for data input but use number formatting of "00\:00" to display the number as if it were a time in hours and minutes. You could also use validation to request the input of an integer between 0000 and 2400. The appearance of the number may be OK but as a datetime it is not relevant being a date sometime prior to 27 July 1906. To convert to a time you divide by 100, treat the decimal part as 60ths to get a result in hours and then further divide by 24 to get the time as a proportion of a day. This could be a worksheet formula, in which case you commit a further range to display the times, or you could simply use a defined name inputTime = DOLLARDE(input/100, 60)/24 If you use 'inputTime' to reference the 4 digit times it will return a meaningful array of times. If you have 365 and feel more adventurous you could define a Lambda function to convert the 4-digit integer representation to Excel times Worksheet formula = CONVERT.TIMEλ(input) where CONVERT.TIMEλ = LAMBDA(integerTime, DOLLARDE(integerTime/100, 60)/24) If you never perform calculation using the non-standard representation of time, everything after the first two paragraphs can be ignored.Playing with conditional formatting
This formatting is something I used to restructure the way I wrote formulas between 2015 and 2018. In the original I used VBA but now, as often happens, worksheet formulas used within conditional formatting are sufficient. The formula defining the condition is = DIRECTREFλ(A1) //Function to test for the presence of direct cell references DIRECTREFλ = LAMBDA(cell, IF(ISFORMULA(cell), REGEXTEST(FORMULATEXT(cell), "\b[A-Z]{1,3}\${0,1}\d{1,6}\b")) ); The original was pretty effective as a training aid to avoid the practice of relative referencing and use defined names! That said, not many followed my lead! Mind you when I posted a discussion on Chandoo it didn't meet with overwhelming support, though subsequent events in the form of spilt ranges have made my life so much easier! The post did however stimulate more discussion than I had anticipated. The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss? | Chandoo.org Excel Forums - Become Awesome in Excel
