Or.............
In cell W38, enter this simply formula and copied down :
=SUMIFS(S:S,Q:Q,W$37,R:R,V38)
Regards
Posts by bosco_yip
-
-
Or,
In N2, formula copied right to O2 and all copied down :
=0+REPLACE(G2,LEN(G2)-1,," ")
Then, select cells range >> "Format Cells" choose "Time"
Regards
-
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
-
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
-
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
-
To extract 2 or 3 digits number at the last of a string
Try this one :
=MAX(0+RIGHT(D1,{2,3}))
Regards
-
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
-
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