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