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

Search results

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

    Problem with XIRR formula

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

    Problem with XIRR formula

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

    Problem with XIRR formula

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

    Problem with XIRR formula

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

    need series with repeating value

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

    need series with repeating value

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

    need series with repeating value

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

    convert matrix to list

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

    need series with repeating value

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

    Count of values corresponding to maximum value of another column for multiple items

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