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

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    Also: =TEXT(COUNTIF(BJ2:INDEX(A2:BJ2,1+MATCH(1,0/(IF(A2:BJ2="","wip",A2:BJ2)<>"wip"))),"wip"),"[=1]0 \Da\y;0 \Da\y\s") Regards
  2. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    But then the results given by Excel Wizard's first formula are all correct, no? You appear to have added your expected results whilst those columns were still hidden. Regards
  3. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    If we are talking about the file you attached in post #14, that is not true: columns F, G, M, N, T, U, AA and AB are all hidden, and thus being excluded from your desired results. Regards
  4. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    You'll have to explain better. I still don't understand why some columns are hidden. Are these columns to be ignored? If so, on what basis? Regards
  5. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    Not sure I understand. Do you realise that your expected results are based on the fact that you've hidden several columns? As I said, my previous formula will not work on ranges comprising more than 15 columns, though Excel Wizard's formula seems to be giving perfectly good results to me, after...
  6. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    Assuming the range in question does not exceed 15 columns: =TEXT(IF(LOOKUP("ω",A2:J2)="wip",FIND(0,NPV(-0.9,N(A2:J2="wip")))-1,0),"[=1]0 ""Day"";0 ""Days""") Regards
  7. XOR LX

    Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

    Hi, =TEXT(COLUMNS(A2:J2)-MATCH(1,0/(A2:J2<>"wip")),"[=1]0 ""Day"";0 ""Days""") Regards
  8. XOR LX

    Performance - formula referencing multiple tables

    Hi, I seem to recall (though I can't locate the documentation now) that one of the (sadly, still considerable) disadvantages of Structured Referencing in formulas is that the necessary construction for absolute column referencing, for example: tbl_TB_Month[@[Department]:[Department]] is...
  9. XOR LX

    Linear Interpolation (2 input, 1 output)

    One option: =AGGREGATE(16,6,LARGE(IF(C$2:E$2=H3,C$3:E$8),MATCH(G3,B$3:B$8)+{0,1}),1-MOD(G3,100)/100) Regards
  10. XOR LX

    Hashtag seperator

    Hi, Office 365: =TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b[contains(., '#')]")) Else, Excel 2013 and later: =INDEX(FILTERXML("<a><b>"&SUBSTITUTE($A1," ","</b><b>")&"</b></a>","//b[contains(., '#')]"),COLUMNS($A1:A1)) and copied to the right. Regards
  11. XOR LX

    Get The Number and Text After Spesific Words

    Hi, In B2: If you have Office 365: =TEXT(TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b></a>","//b[preceding::*[1]=""RT""]")),"000") which will spill to the right automatically. If not, for Excel 2013 and later: =IFERROR(TEXT(INDEX(FILTERXML("<a><b>"&SUBSTITUTE($A2,"...
  12. XOR LX

    Sum Consecutive negative numbers upto 4 years

    We could also employ OFFSET, which often gets a hard press for being volatile (perhaps justifiably), though is a very useful and flexible function: =IF(A1<0,SUM(OFFSET(A1,,,,-TEXT(MOD(COUNT($A1:A1)-IFERROR(MATCH(1,0/($A1:A1>=0)),0),4),"[=0]\4;0"))),A1) though here I am abusing the unintended...
  13. XOR LX

    Sum Consecutive negative numbers upto 4 years

    Hi, Assuming your values are in A1:J1: =IF(A1<0,SUM(A1:INDEX($A1:$J1,COUNT($A1:A1)-IFERROR(1/(1/(MOD(COUNTIF(A1:INDEX($A1:$J1,IFERROR(MATCH(1,0/($A1:A1>=0)),1)),"<0")-1,4))),0))),A1) and copied to the right. Note that this formula may require committing with CTRL+SHIFT+ENTER, depending on...
  14. XOR LX

    Linear Interpolation (2 input, 1 output)

    If the entries in B3:B8 are fixed at intervals of 100 we can use: =PERCENTILE(LARGE(IF(C$2:E$2=H3,C$3:E$8),MATCH(G3,B$3:B$8)+{0,1}),1-MOD(G3,100)/100) Regards
  15. XOR LX

    Linear Interpolation (2 input, 1 output)

    Just because I wasn't assuming access to Office 365 dynamic array functionality, versions prior to which would require the coercion. Regards
  16. XOR LX

    Linear Interpolation (2 input, 1 output)

    A shorter alternative for the non-volatile option: =TREND(LARGE(IF(C$2:E$2=H3,C$3:E$8),MATCH(G3,B$3:B$8)+{0,1}),INDEX(B$3:B$8,N(IF(1,MATCH(G3,B$3:B$8)+{0,1}))),G3) Regards
  17. XOR LX

    Find out the previous date to a date if the criteria matches

    Whatever the reason, we can be certain that the new dynamic array functionality plays a part: the 'non-dynamic' (and also non-CSE, FWIW): =INDEX(MAXIFS(PaidVisit[Bill Date],PaidVisit[Bill Date],"<="&FollowUp[Bill Date],PaidVisit[Patient No.],FollowUp[Patient...
  18. XOR LX

    Find out the previous date to a date if the criteria matches

    Very interesting behaviour. I can't find any documentation on this, nor can I imagine why the simple act of defining criterionValue as a Name would force it to resolve to just its first element only within the main formula. FWIW, we can coerce the correct array return, viz, for example...
  19. XOR LX

    Looking for a Formula.

    Hi, Non-volatile alternative...
  20. XOR LX

    DATETIME Difference calculation

    Thanks @John Jairo V! :) I don't always remember all of the various date formats! Cheers
  21. XOR LX

    DATETIME Difference calculation

    Hi, As a start: =MMULT(TEXT(SUBSTITUTE(B2:C2,":",""),"0\/00\/00\ 00\:00\:00")+0,{-1;1}) Format the cell as you wish, e.g. [m], [h]. Regards
  22. XOR LX

    Repeated and Restart Text

    You're welcome!
  23. XOR LX

    Repeated and Restart Text

    Hi, So only ever two criteria? Never more? If so, in B6: =IF(INT(MOD(ROWS(B$6:B6)-1,C$2+C$3)/C$2),B$3,B$2) and copied down. Regards
  24. XOR LX

    XIRR Challenge

    It works for me using the workbook provided. Perhaps you've attempted to adapt it to a different workbook? If so, I recommend that you post it. Regards
Back
Top