# Posts by bosco_yip

• ## How to create an excel formula that will average the 2 highest values that are separated by a 3 week difference in testing date

Or try this formula solution for average of the 2 largest values separated by 3 weeks (21 days) or over.

=(MAX(C2:C14)+AGGREGATE(14,6,C2:C14/((INDEX(B2:B14,MATCH(MAX(C2:C14),C2:C14,0))-B2:B14)>=21),1))/2

Regards

Or,

B2, copied down :

=LEFT(A2,FIND("-",A2))&TEXT(LOOKUP(9^9,-RIGHT(A2,ROW(\$1:\$100))),"[<10]00")

Regards

• ## INCREMENT NUMERICAL STRING BY 1

Or.............

In B13, formula copied down to B15

=IF(C13="","",INDEX(B\$1:B12,MATCH(9^9,A\$1:A12))&"."&COUNTA(INDEX(D\$1:D12,MATCH(9^9,A\$1:A12)):D13))

Then, select B13:B15 >> copy/paste the formula to B17, B21,B25, B37, B41,B45 and B49

Regards

Bosco

• ## extracting data from a sentence-please needed ASAP

This modified formula solution similar to your post #4 formula

In H2, formula copied across right to L2 and all copied down :

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("║"&\$A2,"NF‰","║"),"%2D (2A",""),"3A",""),"║",REPT(" ",99)),COLUMN(A1)*99,99))

Regards

Bosco

• ## Set cell value using based on cell length

To extract 2 or 3 digits number at the last of a string

Try this one :

=MAX(0+RIGHT(D1,{2,3}))

Regards

• ## Expand multilevel information

Here is a formula solution as in :

1] In "Output" G4, formula copied across to J4 and all copied down :

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(\$4:\$14)/((0+TEXT(COUNTIF(\$B\$18:\$B\$30,\$E\$4:\$E\$14),"[=]1;0"))>=COLUMN(\$1:\$1)),ROWS(\$1:1))),"")

2] In "Output" K4, formula copied across to P4 and all copied down :

=IFERROR(INDEX(\$C:\$C,AGGREGATE(15,6,ROW(\$C\$18:\$C\$30)/(\$B\$18:\$B\$30=J4),COUNTIFS(\$H\$4:\$H4,\$H4,J\$4:J4,J4))),"")

Regards

Expand Multilevel_rev1(BY).xlsx

• ## Extracting last set of numbers from string

Or, try this shorter and non-array formula, which will obtain the same result as per above array formula :

=LOOKUP(9^9,0+MID(A1,FIND("x",A1)+1,ROW(\$1:\$250)))

Regards