Posts by bosco_yip

• Sumif function not working.Returning zero

Try,

=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>7)* Sheet1!D2:D27)

Or,

=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>30)* Sheet1!D2:D27)

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

If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.

Or, try to use INDIRECT function :

=INDIRECT("JCB!B"&MATCH(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

.. to extract one more value from the second column cell (B1134)..........................

To extract B1134 date value in Offset from the cell number AP1134 :

=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-INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))

• Count number of dates by month

Try,

C2, array (CSE) entry :

=SUM(IF((A2:A20>0)*MONTH(A2:A20)=B2,1))

or,

D2, normal entry :

=SUMPRODUCT((A2:A20>0)*(MONTH(A2:A20)=B2))

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