# Posts by bosco_yip

• ## How to automatically convert (92 Hours 29 Min) in to Minutes

Try,

In E2, formula copied down :

=IFERROR(LEFT(A2,FIND("H",A2)-2),)*60-IFERROR(LOOKUP(1,-RIGHT(LEFT(A2,FIND("M",A2)-2),ROW(\$1:\$99))),)

• ## Formula to pull data with multiple horizontal and vertical criteria

In "Summary" sheet C6, formula copied right to D6 and all copied down :

=SUMIFS(INDEX(Data!\$D\$4:\$H\$13,,MATCH(C\$5,Data!\$C\$1:\$H\$1,0)),Data!\$A\$4:\$A\$13,\$A6,Data!\$B\$4:\$B\$13,\$B6)

• ## Issue with copying index & match formula across columns

This is a 2 ways Lookup, using Index + Match function

Try to use this modified formula instead :

=INDEX(source!\$B\$4:\$GZ\$521,MATCH(\$A15,source!\$A\$4:\$A\$521,0),MATCH(1,INDEX((L\$14=source!\$B\$3:\$GZ\$3)*(L\$13=source!\$B\$2:\$GZ\$2),0),0))

Regards

• ## Multiple vlookup

Try,

In "Sheet1" D2, formula copied down :

=IF(VLOOKUP(B2,Sheet2!B:Y,24,0)=C2,VLOOKUP(B2,Sheet2!B:Y,23,0),"")

• ## Partial Match formula based on words in 2 cells

Try,

In C2, formula copied down :

=IF((MAX(MMULT(--ISNUMBER(SEARCH(MID(A2,COLUMN(INDIRECT("C1:C"&LEN(A2),)),1),B2)),ROW(INDIRECT("1:"&LEN(A2)))^0))-LEN(A2)+LEN(SUBSTITUTE(A2," ","")))/LEN(A2)>0.5,"Match","No Match")

• ## Conditional rearrangement of data

Try formula solution ,

1] In "Sheet1" E4, formula copied right to F4 and all copied down :

=IFERROR(INDEX(A\$4:A\$24,AGGREGATE(15,6,ROW(\$A\$1:\$A\$21)/((TEXT(\$A\$3:\$A\$23,"0;;0;\0")="0")),ROW(\$A1))),"")

2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :

=IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))

• ## Adjust Lookup formula to find first entry

Try to use Index+Match function to return the first entry date, as in :

=IFERROR(INDEX(\$B\$1:\$IM\$1,MATCH(TRUE,INDEX(B2:IM2<>"",0),0)),"")

Regards

• ## Remove non-numeric characters before first numeric character

In B2, formula copied down :

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),99)

• ## Last 5 games scoring average

Try,

1] "RF Last 5 Games", in cell J2 CSE formula copied down :

=AVERAGE(INDEX(IF(\$A\$2:\$A\$866=I2,\$C\$2:\$C\$866,0)+IF(\$B\$2:\$B\$866=I2,\$D\$2:\$D\$866,0),N(IF(1,AGGREGATE(14,6,ROW(A\$2:A\$866)-ROW(A\$1)/(IF(\$A\$2:\$A\$866=I2,\$C\$2:\$C\$866,0)+IF(\$B\$2:\$B\$866=I2,\$D\$2:\$D\$866,0)),ROW(\$A\$1:\$A\$5))))))

2] "RA Last 5 Games", in cell K2 CSE formula copied down :

=AVERAGE(INDEX(IF(\$A\$2:\$A\$866=I2,\$D\$2:\$D\$866,0)+IF(\$B\$2:\$B\$866=I2,\$C\$2:\$C\$866,0),N(IF(1,AGGREGATE(14,6,ROW(A\$2:A\$866)-ROW(A\$1)/(IF(\$A\$2:\$A\$866=I2,\$D\$2:\$D\$866,0)+IF(\$B\$2:\$B\$866=I2,\$C\$2:\$C\$866,0)),ROW(\$A\$1:\$A\$5))))))

3] "RF Last 10 Games", in cell L2 CSE formula copied down :

=AVERAGE(INDEX(IF(\$A\$2:\$A\$866=I2,\$C\$2:\$C\$866,0)+IF(\$B\$2:\$B\$866=I2,\$D\$2:\$D\$866,0),N(IF(1,AGGREGATE(14,6,ROW(A\$2:A\$866)-ROW(A\$1)/(IF(\$A\$2:\$A\$866=I2,\$C\$2:\$C\$866,0)+IF(\$B\$2:\$B\$866=I2,\$D\$2:\$D\$866,0)),ROW(\$A\$1:\$A\$10))))))

4] "RA Last10 Games", in cell M2 CSE formula copied down :

=AVERAGE(INDEX(IF(\$A\$2:\$A\$866=I2,\$D\$2:\$D\$866,0)+IF(\$B\$2:\$B\$866=I2,\$C\$2:\$C\$866,0),N(IF(1,AGGREGATE(14,6,ROW(A\$2:A\$866)-ROW(A\$1)/(IF(\$A\$2:\$A\$866=I2,\$D\$2:\$D\$866,0)+IF(\$B\$2:\$B\$866=I2,\$C\$2:\$C\$866,0)),ROW(\$A\$1:\$A\$10))))))

Attached file :

Average last 5 and 10 scoring.xlsx

• ## Average Data Between Certain Months

One last question.....if I have only monthly totals instead of daily how would my formula change. I used the existing one and it returned nothing.

Attached file for example.....

Palchy

Since your header range is changed to B1:N1, the Offset 5th argument must changed to 13 in match with the size of range,

So,

The D6 formula changed to >>

=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET(\$B\$1,MATCH(\$C6,\$A\$2:\$A\$3,0),,,13),\$B\$1:\$N\$1,">="&A\$6,\$B\$1:\$N\$1,"<="&B\$6),1)),"")

• ## Last non-empty cell in the column based on three header rows that are criteria

Very Perfect ...Thnx

one more quick question ......How can I get offset value....

e.g. This formula is extracting value from the cell number AP1134... how can i get it from the third offset column

that is cell number AR1134

Just add 2 in the OFFSET 3rd argument, and the formula become >>

=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))

• ## Last non-empty cell in the column based on three header rows that are criteria

The revised formula :

=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000))

• ## Last non-empty cell in the column based on three header rows that are criteria

The above formula return the position of the desire column

and this formula return the last non-empty cell value in the range of JCB!\$C\$5:\$CB\$5 :

=LOOKUP(9^9,JCB!\$C\$5:\$CB\$5/(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6=JCB!\$C\$1:\$CB\$1&JCB!\$C\$2:\$CB\$2&JCB!\$C\$3:\$CB\$3))

Regards

• ## Last non-empty cell in the column based on three header rows that are criteria

Maybe try,

=LOOKUP(9^9,COLUMN(JCB!\$C\$1:\$CB\$1)-COLUMN(JCB!\$C\$1)+1/(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6=JCB!\$C\$1:\$CB\$1&JCB!\$C\$2:\$CB\$2&JCB!\$C\$3:\$CB\$3))

• ## Average Data Between Certain Months

From this :

=ROUND(AVERAGEIFS(\$C\$2:\$OG\$2,\$C\$1:\$OG\$1,">="&A16,\$C\$1:\$OG\$1,"<="&B16),1)

Into this :

=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET(\$C\$1,MATCH(\$C16,\$B\$2:\$B\$7,0),,,395),\$C\$1:\$OG\$1,">="&A\$16,\$C\$1:\$OG\$1,"<="&B\$16),1)),"")

Then,

copied to I16, and all copied down

Regards

• ## Calculating a numeric score from a qualitative score

Remove trailing spaces in Q2:Q4,

Then,

In L2, formula copied down :

=SUMPRODUCT(SUMIF(Q:Q,B2:K2,T:T))

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.

2 options,

1] You can use a Textjoin UDF ( using Google to search)

2] Used a longer formula or helper columns to obtain the target.

Regards

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

Using Textjoin function for the above formula :

1] Excel 2019, required CSE entry.

2] Office 365 with dynamic functions subscribed, CSE entry doesn't required

3] Office 365 without dynamic functions subscribed, required CSE entry.

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

Try,

In G2, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH(F2&CHAR(10),B\$2:B\$10&CHAR(10))),A\$2:A\$10,""))

• ## Formula to convert a gregorian date to a chinese date

To convert a Gregorian date to a Chinese date

In B3, enter formula :

=TEXT(A3,"[\$-130000]d mmm e")