Posts by bosco_yip
-
-
-
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
-
-
-
-
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))
-
-
-
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
-
-
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))
-