User Profile
fastexcel
Brass Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Shift+F9 is now cleaning dirty cells (after an update from version 2305 to 2308)
Wow - I originally wrote that article 15 years ago - great to see that it is being updated. Done some more testing - there is a calc bug but its not new.. Sheet 1 has a volatile function (I used NOW() ) Sheet 2 has a formula that is downstream dependent on the volatile function. Calc is Automatic Using sheet calc on sheet 1 calcs the volatile function but the sheet 2 formula does not recalc (which it should) and the status bar shows Calculate Pressing F9 resolves the bug.1.6KViews0likes1CommentRe: Terrible recalculation speed on Ryzen processor
Multi-threaded recalc occurs in 2 steps. Step 1 works out how to allocate the calc between threads Step 2 then actually does the calc. Usually the results of previous Step 1 optimisations are used as a starting point for subsequent recalcs so the time taken to do step 1 diminishes on subsequent recalcs. Difficult to make any sensible comments without looking at the actual workbook (would be happy to do that if possible) Charles Williams4.5KViews0likes0CommentsRe: Not able to create a double unary formula on Excel for Mac v16.36
jayaramv The first parameter has a > operator which means that the result of the expression will be an array of True or False. SUMPRODUCT will error with True/False so the True/False needs to be converted to numbers, which is done using the --. The other parameters do not have a comparison operator so do not need this.2.4KViews0likes0CommentsRe: Not able to create a double unary formula on Excel for Mac v16.36
jayaramv Thanks for the screenshot: I had a mistake in the formula and also forgot that N() does not return an array. And you don't need the N if B3 is zero. I think this formula gives you the correct result. You do not need to array-enter it since SUMPRODUCT always calculates in array fashion. Also you don't actually need the -- in --(B14-$A4:$A8) since there are no True/False to convert to 1s and zeros. =SUMPRODUCT(--(B14>$A4:$A8),--(B14-$A4:$A8),(B$4:B$8-B$3:B$7) )2.6KViews1like5CommentsRe: Not able to create a double unary formula on Excel for Mac v16.36
jayaramv Excel does not allow range references inside { } - you can only put constants inside the {} syntax. You need to convert your formula to an Excel-style array formula. If you have Dynamic Array Excel it will automagically be enetered as an array formula, otherwise you need to use Control-Shift-Enter to enter the bformula2.5KViews1like14CommentsRe: Getting the previous spill range of a dynamic array function
pyxll My (not-yet-implemented) design for formatting dynamic arrays does this by storing the spill dimensions of the spill ref as cell metadata using the aftercalculate event. The cell metadata could be stored in various ways. To make it persist maybe define a Name for the spill ref and then use the Name as a key to an array/list/dictionary/collection thing that could be persisted as custom xml parts. Or for a non-persistent kludge use Cell.ID1.3KViews0likes1Comment
Recent Blog Articles
No content to show