Posts by bosco_yip
-
-
-
-
-
-
Maybe,
In B2, CSE formula (Ctrl+Shift+Enter) copied down :
=MAX(FREQUENCY(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0),ROW(INDIRECT("1:"&LEN(A2)))))>=3
Or,
it can be shortened to :
=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2)))>=3
Regards
-
-
Thank u it worked but can u explain to me
To understand how does the two formula worked ?
Try to use the "Evaluate Formula" feature, that resides on the Formulas tab, in the "Formula Auditing" group.
All you need to do is click the Evaluate button and examine the value of the underlined formula part.
The result of the most recent evaluation appears in italics.
Continue clicking the Evaluate button until each part of your formula has been tested.
Regards
-
-
-
-
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))