User Profile
PeterBartholomew1
Silver Contributor
Joined 8 years ago
User Widgets
Recent Discussions
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 ExcelRe: Native FIFO Inventory Costing Function for Excel (Proof of Concept with LAMBDA)
jmarraztoa I suspect that most users' understanding of the options is still constrained by the limitations of the conventional spreadsheet. Your work is interesting. You appear to have used recursion, whereas I used SCAN working over a stacked and sorted array of inputs and outputs. FIFO Lambda in Excel The other difference is that you appear to have experience of FIFO and LIFO calculations whereas I had ideas for the solution but no experience of the problem! I provided an option for selecting the form of presentation by allowing the user to switch between crosstab and list formats but with no specific end use in mind. 😒 Given a function that performs the FIFO calculation, it should be possible to go further, say by using the function within GROUPBY to build an array of functions that would calculate the results for multiple products or distinct geographical regions. Either the entire set of results could be output as an array of arrays, or slicers could be used to down-select. My feeling is that, this far, I have only scratched the surface of what is possible using modern excel.Re: Please update Excel to handle more than 15 digit numbers!
The limit is determined by the storage allocated to hold a number in a standard IEEE format. There is always a compromise to be struck between the storage demands & processing time required for a calculation, and the accuracy achieved. Some calculations such as the solution of large ill-conditioned systems of simultaneous equations can run into problems but I think it very unlikely that you are concerned with the precision to which numbers are represented. It appears to me that the 'numbers' you wish to manipulate are in fact text strings that happen to based upon the digits 0-9 rather than A-Z. In which case the limit appears to be 32,767 of which 1024 may be displayed as a single row within the cell. This is not a workaround, a credit card number cannot be meaningfully doubled or square-rooted, so text is the appropriate format. The catch is that Excel is like an eager puppy in the way it attempts to convert anything that could be considered as a number in order to perform numerical calculation. SergeiBaklan has shown ways of suppressing this behaviour by setting options. Another approach is to prefix a possible number by a single apostrophe, say when loading the data from VBA. Once Excel is allowed to convert the text to a number the original text cannot usually be recovered and would need to be read once more from the database.Re: TOCOL/TOROW Treat Thunks as Errors
I had somewhat resigned myself to the idea that I couldn't use the TOCOL error criterion to filter errors without also taking out the #CALC! errors that represent functions. I now think I was wrong to settle for that! As you have observed, ISERROR does not treat functions as errors so why should TOCOL? In a functional programming environment in which functions are first class citizens it is simply wrong that TOCOL should treat them as errors.
