Maybe,
In I6 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET($A$4,MATCH($H6,Company,0),2,,2)
Posts by bosco_yip
-
-
Return the last row of text and remove the starting 5 words, formula solution,
In B2 enter formula :
=TRIM(MID(SUBSTITUTE(" "&MID(A2,1-LOOKUP(1,-FIND(CHAR(10),A2,ROW(A:A))),599)," ",REPT(" ",49)),299,599))
-
Another option using Sum + Offset function
In D4, formula copied down :
=SUM(OFFSET(F$3,MATCH(B4,$F$4:$F$9,0),MONTH(C4)+(DAY(C4)>10),1,13+(DAY(C4)>10)-MONTH(C4)))
Remark :
It is better to add ….MATCH(B4,$F$4:$F$9,0)… inside the Offset formula to find the Row position of the Company name in the source list
Regards
-
In C35, formula copied across and down :
=SUMPRODUCT(-TEXT(LEFT(TEXT(MID(C$3:C$33&"@",FIND($A35&"-",C$3:C$33&$A35&"-")+1,COLUMN($A:$I)),),COLUMN($A:$I)-1),"[<>];;;\0"))
-
In J3, array formula (CSE) copied across right to M3 and all copied down :
=IFERROR(TEXTJOIN("/",,IF($C3:$I3=J$2,$C$2:$I$2,"")),"")
-
Maybe,
In C2, formula copied down :
=LOOKUP(B2,OFFSET(INDEX($I:$I,MATCH(A2,$G:$G,0)),0,0,4,3))
-
Or try this non-array formula
In A1, formula copied down :
=IFERROR(LOOKUP(1,SEARCH(L$1:L$4,B1),M$1:M$4),"")
Regards
-
Try,
In H4, enter formula :
=AGGREGATE(15,6,H9:H30/(D9:D30=D4)/(E9:E30=E4)/(F9:F30=F4)/(G9:G30=G4)/(H9:H30>=C4),1)
Regards
-
Maybe try this formula solution
In "SUMMARY" sheet cell A100, enter formula and copied across to F100 :
=IFERROR(INDEX(INVOICES!A:A,AGGREGATE(15,6,ROW(INVOICES!$A$4:$A$40)/(INVOICES!$F$4:$F$40<>""),ROW(A1))),"")
And,
Select cell C100:F100 >> Custom Cell Format, enter : #,##0.00;;;
Then,
Select A100:F100, all copied down to row line no. 136
>> Finish
-
In D8 formula copied down :
=(1+IFERROR(VLOOKUP(VLOOKUP(MONTH(C8),$H$8:$I$19,2,0) & "-" & YEAR(C8),$A$2:$B$61,2,0),B$2))^(1/12)
Regards
-
Extract top 4 from the last 15 in the list (new values will be added to the end of the list)
Assume data in A2:A?
In C2, enter formula and copied down 3 lines :
=LARGE(OFFSET(A$1,MATCH(9^9,A:A)-1,0,-15),ROW(A1))
-
-
In C9, enter formula :
=LOOKUP(9^9,B3:B5/(A3:A5=A9)/(C3:C5<=B9)/(D3:D5>=B9))
-
Try,
In "Sheet2" E2 formula copied down :
=VLOOKUP(A2,Sheet1!A:B,2,0)
-
Try,
In F2, enter formula :
=SUMPRODUCT((D2:D20<>"")*(B2:B20<>"")*(C2:C20<>""),A2:A20)
the formula result will return 496
-
Try,
1] In A15, copied right to B15 and all copied down :
=INDEX(A$4:A$11,MATCH(0,INDEX(COUNTIF(A$14:A14,A$4:A$11),0),0))
2] In C15, copied across right to AG15 and all copied down :
=INDEX(C$4:C$11,AGGREGATE(15,6,ROW($B$4:$B$11)-ROW($B$3)/($B$4:$B$11=$B15)/(C$4:C$11<>""),1))
-
Maybe,
1] C5, keep empty
2] In C6, formula copied down to C57 :
=IFERROR(1/(1/(INT((ROW(A1)-1)/D$5)*F$5)),"")
Regards
-
Try.............
1] If you have Office 365 or Excel 2019 CONCAT function, use this in H2 and copied down :
=""""&CONCAT(INDEX($A$1:$F2,N(IF(1,IF({1,0},1,ROW(A2)))),MATCH(G2,A2:F2,0)+1)&" ")&""""
Or,
2] If you don't have CONCAT function, use this in H2 and copied down :
=""""&OFFSET($A2,ROW(A$1)-ROW(A2),MATCH(G2,A2:F2,0))&" "&OFFSET($A2,0,MATCH(G2,A2:F2,0))&""""
-
Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.
Then,
D2, formula copied down :
=SUMPRODUCT((A$2:A$10=A2)*(A$1:A$9=A$2:A$10)*N(+C$2:C$10)/(COUNTIF(A$2:A$10,A2)-1))
-
Try...………………...
1] In C2, formula copied down :
=IF(A2=A1,B2-B1,0)
2] In D2, formula copied down :
=AVERAGEIFS($C$2:$C$10,$C$2:$C$10,"<>0",$A$2:$A$10,A2)