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")

Re: Find position of first zero in a sequence of three consecutive zeros

Try this array formula

=MATCH(3,COUNTIF(OFFSET(A1,0,COLUMN(A1:X1)-COLUMN(A1),1,3),0),0)

confirmed with CTRL+SHIFT+ENTER

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: Increment numeric part of criteria in SUMIF function as it is copied down.

Often it's better to list the criteria in another column, for example put "week 1" (without quotes) in A2 of your summary sheet and copy down to A53 or as far as required. Now formula in B2 copied down can be

=SUMIF(Sales!\$B:\$B,A2,Sales!I:I)

Re: Add &amp; Subtract alternating columns from each other

Do you have headers? If row 3, for instance has "Qty In" and "Qty out" alternating then you can use 2 SUMIFS like this

=SUMIF(C\$3:DC\$3,"Qty In",C4:DC4)-SUMIF(C\$3:DC\$3,"Qty Out",C4:DC4)

Re: Find min and max date based on conditions

C1:C5 can only contain numbers otherwise you'll get an error. If row 1 has headers then start with row 2, i.e.

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

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: How does one specify and/or limit a cell value when multiplying matrices.

See attached file.

I only changed S43:S52 by array entering my suggested formula in that range. The results are 328;0;1;1;1;150000;94;0;0 [rounded]. Isn't that what you expect?

## Files

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: How does one specify and/or limit a cell value when multiplying matrices.

Would this do it?

=IF(ROW(R43:R52)=ROW(R48),R48,MMULT(\$G\$43:\$P\$52,R43:R52))

array entered in S43:S52

Re: ignore text values whenusing lookup function

I'm not 100% sure what you want to do. For the last non-blank value in A1:Z1, text or number, try this formula

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

Re: HLOOKUP + RANDBETWEEN formula help

Hello Tim, try this formula

=IF(K3="random",INDEX(\$AA\$3:\$AH\$48,RANDBETWEEN(1,COUNTA(INDEX(\$AA\$3:\$AH\$48,0,MATCH(IF(\$L\$1=0,1,\$L\$1),\$AA\$2:\$AH\$2,0)))),MATCH(IF(\$L\$1=0,1,\$L\$1),\$AA\$2:\$AH\$2,0)),L6)

Re: Sumproduct to Subtotal

Try this formula

=SUMPRODUCT((A2:A5000=J7)*(D2:D5000=I11),SUBTOTAL(9,OFFSET(E2,ROW(E2:E5000)-ROW(E2),0)))

doesn't need CSE

Re: Sumproduct to Subtotal

To total the visible values in column E you can use

=SUBTOTAL(9,E2:E1000)

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: Match a value not from a contiguous range

When you say "nearest" do you mean strictly nearest or do you want to match with the next highest value, i.e. what would the result be if A1 = 3100 or 3500?

Re: Calculate time interval based on start and end date and time

See attached file for examples with the formula I suggested. You say working day is 8:30 to 6:30, that's 10 hours not 9, are you factoring in a lunch break, my formula doesn't do that.....

## Files

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, 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