Recent content by Lori

@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...