# Posts by bosco_yip

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

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.

2 options,

1] You can use a Textjoin UDF ( using Google to search)

2] Used a longer formula or helper columns to obtain the target.

Regards

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

Using Textjoin function for the above formula :

1] Excel 2019, required CSE entry.

2] Office 365 with dynamic functions subscribed, CSE entry doesn't required

3] Office 365 without dynamic functions subscribed, required CSE entry.

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

Try,

In G2, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH(F2&CHAR(10),B\$2:B\$10&CHAR(10))),A\$2:A\$10,""))

• ## Formula to convert a gregorian date to a chinese date

To convert a Gregorian date to a Chinese date

In B3, enter formula :

=TEXT(A3,"[\$-130000]d mmm e")

• ## Required a Formula to find out at least 3 times repeated values in a Single cell

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

• ## Required a Formula to find out at least 3 times repeated values in a Single cell

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

• ## Dropdown Menu & VLookup formula not working

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

• ## Dropdown Menu & VLookup formula not working

1] Change B2, data validation formula into :

=OFFSET(C9,MATCH(A2,B10:B22,0),,COUNTIF(B10:B22,A2))

2] In C2, formula copied across right to E2

• ## Dependent dropdown lists with multiple words in the different sheets

Maybe,

In I6 >> Data Validation >>

>> Allow : List

>> Source : =OFFSET(\$A\$4,MATCH(\$H6,Company,0),2,,2)

• ## Returning the last row of text in a cell, minus the first few words

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

• ## Calculate ONLY if before the 10th day of month

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

• ## Modifying a formula

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

• ## EXCEL FORMULA

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,"")),"")

• ## data array with date range and return text value in result

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

• ## Large function with criteria.

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

• ## If cell is not empty, copy row...

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

TEST (BY).xlsx

• ## Vlookup formula does not work for some cell

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

• ## dynamic list length to find top 8 from last 20

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