Search results

@XOR LX Agreed, after looking at the BigNum search results i still don't see why one would possibly want to choose a value consisting of 21 characters when a single digit will do? In general choosing 'the least integer greater than all values in the lookup array' gives 10^308 for arbitrary...

That's the largest numeric constant but also may give a misleading impression of reliability as bigger numbers are easily created with formulas, eg '=10^308'. Even if a lookup range contains only constants, the definition BigNum '=9.99999999999999E+307' may fail if BigNum occurs more than once...

@Doug Jenkins Yes, 'LookupEval' works whichever sheet is active now. Making the same replacement in the other 'LookupAvg1','LookupAvg2','LookupAvg3' functions also works for them too. Interesting the behaviour is different in Office 365, on my 2016 set up the udf runs approx 50% faster with...

Interesting analysis. Not thoroughly tested but i think a couple of points discussed in the recent blog post may be relevant here also: 1. For x2 performance a leading plus sign could be used in place of the equals sign within the Evaluate formula (unary plus just dereferences the result). 2...

@Hui, i don't think there's much of a debate to be had. In the same wikipedia link above, spreadsheets are classified as multiparadigm, functional and visual languages. Excel users may not feel like they are programming as the compilation (calculation) and variable declarations (cell...

That's not strictly true. Spreadsheets in general are considered a programming paradigm and are listed as a class of ( cell-oriented ) functional language: https://en.m.wikipedia.org/wiki/List_of_programming_languages_by_type Excel itself is commonly considered by developers to be an IDE and...

@Doug, Nice summary. i hadn't really taken in the VBA requirement - thanks for clarifying that. Much of my experience in this area has been with Exceldna which doesn't need any vba interaction. On a side note, i suppose the vba part could be stored as an addin (*.xlam) if desired and worksheet...

For PQ, i guess you could always use a (heavy-handed) sheet change event. Is there one for a PQ refresh? I can see one for Table update. It would be nice if there was a non-code option so that the workbook wouldn't need to be macro-enabled and so that code only got called when the input cell...

Doug, I second the xlwings approach. It seems simplest overall when compared with formula, vba and PQ and in addition to pairs can return triples and higher combinations. It can also be made fully dynamic to autoresize on calculate which I don't think the other ways can (PQ resizes the table...

David, yes the basic logic of the solution is easier to see in the specific case that you show. As Peter says, the previous formula generalises to more digits by piecing together three basic building blocks: - MODE.MULT which is a goto function for returning variable length arrays and can...

Hi David, For a self-contained formula that returns an array and allows for any number of digits, perhaps this...

NarayanK makes very valid points relating to a general need for data granularity. I must confess I didn't have time to read the whole thread. My comment above derived from the imperfect dichotomy of a siteseeing map (with a grid overlaid and annotations) versus a guidebook with numbered...

Some like to plan trips from guidebooks; others like to work routes out from maps (with an A1-style grid) Different work practices may help reduce errors but then A1-notation improves productivity for the many users who find it easier to work with. So from a company perspective there's a...

Indeed, I firmly agree one should always use names in sheet references in anything other than throwaway code. Aside from the lack of meaning of cell references, all it takes is for a row/column to be added/deleted to break the code. For the other point, using workbook names like Range("a") in...

Great looking template - need to get predictions in quick! Those Chinese language forums have great material (if you can find a decent translation of them) and may be some way ahead of the English ones on level of knowledge. See this thread for example...

this version also seems ok with "guess" based off net value, =XIRR(IFERROR(INDEX(F5:G246,AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),),IFERROR(INDEX(D5:E246,AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),),IF(SUBTOTAL(9,F5:G246)>0,0.1,-0.1))

Looks like it's a deficiency in the method used by the XIRR function as you can check by copying the data from portfolio U to a new sheet and sorting dates manually. XNPV also does not work with this data, however we can use the formula shown in the help on XIRR together with Goal Seek to find...

@GraH Guido I did spend some time trying to find an easier way but failed :( If dates were unique a simple SUMIF function for values based off the array of dates would suffice. Instead, due to the repeated dates, the formula works off the row index and a date/value rank combo to retrieve...

For the single period annualized return an alternative to XIRR(F5:G5,D5:E5) is: =(-G5/F5)^(1/YEARFRAC(D5,E5,3))-1 For the filtered data you could try this with CTRL+SHIFT+ENTER which autosorts dates...

Well, i'm flattered but many of those ideas i can't claim really as mine as they build on work of many others. I learned much from the book "Excel Expert Solutions" back in the 90s that compiled many tips from MVPs of that era - still available from Amazon but now totally out of date. I've been...

Actually, the alternative syntax still creates column precedents they just don't show in the formula tools. And for the static array one can use [r1:r1024] which allows A1 or R1C1 style refs. It's all a bit of a faff though. The proposed SEQ function needs more votes! (9 so far...)

Yes, your 'seq' definition should be fine in general. Only deleting all cells on sheet will affect it. A possible modification that removes any additional cell precedents and sensitivity to worksheet operations is: thisColumn:=!C [This syntax has caveats but is ok when used in conjunction...

One more: Pivot Table (Alt+D+P -> Multiple Consolidation Ranges)

Hi Peter, That discussion was raised in your last challenge thread. Some options that allow for row insertions at the top are: Paste as values (for a static list) ROWS($A$1:A1) filled down from A1 ROW()-MIN(ROW())+1 in any vertical range with CSE Table references (like in your suggestion)...

Or maybe this entered in G2: =SUMPRODUCT((COUNTIFS(B2:B9,B2:B9,C2:C9,">"&C2:C9)=0)*(D2:D9=F2))