Posts by bosco_yip

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

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)

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

Try,

In "Sheet1" D2, formula copied down :

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

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

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

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

In B2, formula copied down :

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

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

Quote from palchy

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

Quote from rehan kazi

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

Based on your attached file.

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

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

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

Change your D16 formula >>

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

Remove trailing spaces in Q2:Q4,

Then,

In L2, formula copied down :

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

Quote from [email protected]

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

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.

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

To convert a Gregorian date to a Chinese date

In B3, enter formula :

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