• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by Lori

  1. L

    INDEX, MATCH value lookup based on >= and <=conditions

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

    INDEX, MATCH value lookup based on >= and <=conditions

    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...
  3. L

    Making use of array formulas in UDFs

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

    Making use of array formulas in UDFs

    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...
  5. L

    Any other program languages as simple as spreadsheets like excel?

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

    Any other program languages as simple as spreadsheets like excel?

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

    Pairs of numbers

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

    Pairs of numbers

    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...
  9. L

    Pairs of numbers

    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...
  10. L

    Pairs of numbers

    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...
  11. L

    Pairs of numbers

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

    The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss?

    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...
  13. L

    The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss?

    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...
  14. L

    VBA Range Discussion

    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...
  15. L

    Excel WC2018 prediction template

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