User Profile
PeterBartholomew1
Silver Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Non-Consecutive Cell Referencing
May I be a bit harsh. In my opinion you are beginning your adventure of learning Excel by focussing upon obsolete techniques, maybe forced upon you by the use of legacy software. In these days of dynamic arrays, the concept of relative referencing an dragging formulas down is over-complicated and confusing! If you wish to reference every 5th element of an 'array', one way of doing it is to wrap the array across 5 cells at a time. wrapped = WRAPROWS(array, 5) You will see the references you require in the first column. To select the column one simply chooses it every5th = CHOOSECOLS(wrapped, 1) These are most useful in situations in which the other columns are used in separate calculations. If they are not used, INDEX will work = INDEX(array, SEQUENCE(n,,, 5)) Putting these together, either by nesting or by using the LET function to define variables every5th = CHOOSECOLS(WRAPROWS(array, 5),1) or (a bit more advanced) every5th = LET( wrapped, WRAPROWS(array, 5), CHOOSECOLS(wrapped, 1) ) every5th = LET( k, SEQUENCE(n,,,5)), INDEX(array, k) ) I am not trying to get at you. It is simple that I believe that you should be encouraged to start your journey by travelling forwards, not back to the past. Maybe the other expert contributors to this discussion have a different opinion.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.Re: Dependent calculations in tables
I usually move away from tables for calculation fields in order to use dynamic arrays. A shorter version of your formula may be obtained by taking the multiplication by the 'total' field outside the conditional statement and also by using the SWITCH function. = total * SWITCH(Cur, "EUR", 1, "NOK", nokeur, "SEK", sekeur, "GBP", gbpeur, 1 ) It is difficult for an end user to tamper with an array formula without destroying it altogether (somewhat conspicuous!)Re: How can I display negative values for time in calculation results
To get time differences to display as hours and minutes (as opposed to a decimal part of a day), the value must be recorded as positive, even it that means using a formula in the form = ABS(end - start) That doesn't prevent you from formatting the number to show as negative (with a minus sign or even with the accountancy trick of a red value within parentheses). A formula-based conditional format using the condition = start > end could be used to apply the number format [Red]([h]:mm) The catch is that, like the text solution, the value in the cell cannot be used directly in dependent formula without correcting the sign. There is another different trick used to express fractions by showing the numerator after the decimal place, with the denominator as given within the function. So = DOLLARFR(24*(end - start), 60) will give -1.45 for a negative time difference of 1 hour 45 minutes.Re: Issues attaching files to posts
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!
