Posts by daddylonglegs

    Re: Find highest 5 numbers in column


    Hello Whacker, that's a very "expensive" formula to copy down 6000 rows....you don't want to do that....


    Try this approach


    first get the 5 (different) highest values......


    In D2


    =MAX(A2:A15)


    In D3 copied to D6


    =LARGE(A$2:A$15,COUNTIF(A$2:A$15,">="&D2)+1)


    Now D2:D6 contains the six highest (different) numbers....and clearly you want to identify those that are >= D6 so for your 6000 row formula you need just


    =IF(A2>=D$6,A2,"Other")


    regards, daddylonglegs

    Re: Formula to count the number of times a non-zero value appears three consecutive t


    These 2 methods should give the same results.....





    =(COLUMNS(B1:P1)-LEN(SUBSTITUTE(B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1,"111","")))/3


    Perhaps not practical if you want to repeat for much larger ranges......if so try this "array formula"


    =SUM(INT(FREQUENCY(IF(B1:P1=1,COLUMN(B1:P1)),IF(B1:P1<>1,COLUMN(B1:P1)))/3))


    confirmed with CTRL+SHIFT+ENTER

    Re: Extract Nth Word from text in cell


    With text in A2 and "n" in B2 (a number), try this formula in C2 to extract the nth word


    =TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A2),FIND("^^",SUBSTITUTE(TRIM(A2)&" "," ","^^",B2))-1)," ",REPT(" ",99)),99))

    Re: Find min and max date based on conditions


    It's not particularly easy to find non-array versions. You can try this for MAX (assuming the MAX will always be a positive value)


    =SUMPRODUCT(MAX(($A$1:$A$5="September")*($C$1:$C$5)))


    but the same syntax for MIN doesn't work, you normally get an unwanted zero.

    Re: ignore text values whenusing lookup function


    It works in a similar way to your original formula


    =lookup(10000,g33:s33)


    Assuming that numbers in G33:S33 are < 10000 that formula will always return the last number in the range. If you use this formula


    =LOOKUP(2,1/(A1:Z1<>""),A1:Z1)


    Then the part (A1:Z1<>"") returns an array of TRUE/FALSE values.....when you divide 1 by that array TRUE acts like 1 and FALSE like zero so you get an array of 1s (where the cell isn't blank) and #DIV/0! errors (where the cell is blank). LOOKUP ignores the errors and matches with the last 1 (i.e. the last non-blank) and then takes the corresponding value from A1:Z1.......

    Re: If noiserror formula


    What do you want to do exactly? There isn't a NOISERROR function, you can use NOT and ISERROR in conjunction, e.g.


    =IF(NOT(ISERROR(A1)),A1,0)


    but it would normally be easier just to reverse that and use ISERROR on it's own.........or possibly some other function like ISNUMBER......but that depends on what you want to achieve.......

    Re: Extract specific numeric portion of text string


    FIND is case-sensitive so it looks specifically for a lower case "k". Unless you have another lowercase "k" followed by a space before the "target" "k" then there shouldn't be a problem.


    I don't get a problem with that string. All the formulas returned 15.5 as expected, which formula were you using?


    I note that that string contains another "k " but as it's later in the text that shouldn't cause a problem.......

    Re: Extract specific numeric portion of text string


    The ISERROR syntax would be like this


    =IF(ISERROR(LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0)),"",LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0))


    or you could use this shorter version to return a zero rather than an error


    =LOOKUP(10^6,CHOOSE({1,2},0,LOOKUP(10^6,RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})+0)))


    Let me explain how the formula works.......


    I used FIND to find the first position of "k " followed by a space and then left function then returns all the text up to the "k " but not including it, so where you have this text in A5


    [COLOR="red"]05 of 10 2.05L BDGO 3Jun10 1000 Slow 2Y MDN-SW 15[/COLOR]k D Yendall (3) 57 ( SW) Btg:$8 $9 $11; 58.89 1-Ello Ello (B J Melham, 5) 57; 2-Legion's Belle (B Park, 4) 55; 3-Booklet (G Boss, 8) 55; 9th rails. Off fence t. Angled clear top strt & hit line hard making good ground to line.


    this part of the formula


    LEFT(A5,FIND("k ",A5)-1)


    returns just the part I marked in red


    Now the clever part........


    RIGHT function is used to give an array consisting of the rightmost 1,2,3,4,5 and 6 characters, so from this part


    [COLOR="red"]05 of 10 2.05L BDGO 3Jun10 1000 Slow 2Y MDN-SW 15[/COLOR]


    the rightmost 6 characters are "-SW 15" so this part


    RIGHT(LEFT(A5,FIND("k ",A5)-1),{1,2,3,4,5,6})


    returns this array


    {"5","15"," 15","W 15","SW 15","-SW 15"}


    Now I add 0 [+0].......and when you add a number to text you get an error so that array turns into this array


    {5,15,15,#VALUE!,#VALUE!,#VALUE!}


    Now I use LOOKUP on that array, i.e.


    LOOKUP(10^6,{5,15,15,#VALUE!,#VALUE!,#VALUE!})


    10^6 is simply 10 to the power of 6 = 1000000


    When you lookup a large value like that in an array containing only smaller values it finds the last value, in this case 15 which is the value you want.......so {1,2,3,4,5,6} accommodates a maximum of a six digit number (or 5 digits with a decimal point)....which I assume is sufficient for your requirements here


    regards, daddylonglegs