New answers tagged

0

Create a drop-down list with the values elapsed calendar days and working days in cell A12. Then use this formula: =ifs( A12 = "elapsed calendar days", days360(D12, E12), A12 = "working days", networkdays(D12, E12), true, iferror(1/0) )


0

Traditional way =LEFT(A1,FIND(CHAR(10)) - 1) OR =LEFT(A1,FIND(CHAR(13)) - 1) Use CHAR(10) for line feed (\n) Use CHAR(13) for carriage return (\r\n )


1

Generally speaking, this would do it: =REGEXEXTRACT(A1,"(.+)\n")


1

Please use the following simple, still improved formula ={"Score"; INDEX(IF(IF(LEN(B2:B), COUNTIF(IF(REGEXEXTRACT(D2:G,"(?i)yes")="yes", ROW(D2:D)), ROW(D2:D)),)=0,"", IF(LEN(B2:B), COUNTIF(IF(REGEXEXTRACT(D2:G,"(?i)yes")="yes", ROW(D2:D)), ROW(D2:D))...


1

Please try the following formula =TEXTJOIN(CHAR(10),1,UNIQUE(INDEX(FLATTEN(IF(G2:K5="",,G1:K1))))) Functions used: INDEX CHAR FLATTEN IF UNIQUE TEXTJOIN


1

You can use the following formula =QUERY(GOOGLEFINANCE("NSE:"&C1&"", "ALL",C2 ,C3),"select Col1, Col3, Col4",1) Functions used: GOOGLEFINANCE QUERY


0

You can use this formula instead =IF(K12="","",SUM(K12 * 4)+(L12 * 9)+(M12 * 4)) This formula says: If cell K12 is empty, show empty. If not, show the result of SUM(K12 * 4)+(L12 * 9)+(M12 * 4))


1

You can use the following =IFERROR(SWITCH(I9:I39,"Bad","Bad","","Not Filled","Excellent","Excellent")) You can also take it a step further =ARRAYFORMULA(IF(C9:C="",,IFERROR(SWITCH(I9:I,"Bad","Bad","","Not Filled","Excellent","...


1

If I understood well, you want to use an Arrayformula while using the AND( , ) function. It won't work, you should use this instead: =ARRAYFORMULA(If((C9:C39<>"")*(I9:I39=""),"Not Filled","")) Note: Within ARRAYFORMULA: to use AND, we use * To use OR, we use + Your first formula could also be simplified as ...


2

Please use =ARRAYFORMULA(IF(LEN(B2:B),B2:B&"-"&COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B)),)) Functions used: INDEX IF ROW LEN COUNTIFS


1

It is likely that your list can be generated in several ways (and probably more efficiently than the following answer). Please consider this as one alternative. I hope that I have not misunderstood your requirements; if so, then please let me know. Insert this formula in Cell I3 (based on the sample data shown below): =query({B3:H7},"select Col1 where ...


1

I think this will do just what you need. Give it a try =QUERY(INDEX(IFERROR(FLATTEN(SPLIT(A2:A9,",")))),"WHERE Col1<>'' ",0)


1

Change your formula to include the blank row3. Like this: =MOD(COUNTA(B3:B),12) Inserting a new row To insert a new row at the top of the range, click on the first row that contains data - in this case, it is Row#4 ("12-Mar"). A new row#4 will be created. Enter the date/data into that cell, and the adjacent cell in Column B. Because the range in ...


0

A similar question was asked in Stackoverflow Google Sheets query return all rows if they match any value in a column on another sheet and the answer by @JPV suits your scenario. Substitute this formula for your existing formula. Note, this formula will create a query output for all matching rows. =query(A1:L,"SELECT B, C WHERE A matches '"&...


1

You want a fast, effienct way to find matches between two sets of order numbers. Let's assume that one set of order numbers are in Sheet1 and this sheet is to be compared to those on Sheet2, and that row 1 in each sheet contains headers. Enter this formula in Sheet1, Cell B2: =arrayformula(iferror(match(A2:A,Sheet2!$A$2:$A,0),"")) This tests for a ...


0

If each product has the same number of date entries, this is quite easy. You can simply unwrap it by indexing into the data (i,j) using the INDEX function. No need for V and HLookups. Say you have 3 products and 4 dates, that means you will have 12 rows: Product 1 Date 1 # = index(data,product#,date#) Product 1 Date 2 # Product 1 Date ...


0

It looks like you can just use =MOD(COUNTA(B4:B),12): (those are 15 X-es, and 15 % 12 = 3)


0

You are tracking items borrowed (OUT) and returned (IN) items. You want to identify items that are currently OUT (and date taken, by whom and location). You might also use this information to record the "Borrowing Status" on the master list. IMO, you are correct to track borrowings (OUT) and returns (IN). This is essential to provide a record of ...


0

Try this formula: =ARRAYFORMULA(QUERY({UNIQUE(A3:A),TRANSPOSE(SUBSTITUTE(TRIM(QUERY(IF(A3:A=TRANSPOSE(UNIQUE(A3:A)),B3:B&",",),,9^99)&"|"),", |",""))},"select Col1,Col2 where Col1 is not null order by Col1 label Col1 'Post Code',Col2 'City/Suburb' ")) Sample data


0

A person works within a given time range (let's call this the "SHIFT"). You want to calculate whether any (and how many) SHIFT hours fall within a second time range (let's call this the "ALLOWANCE PERIOD"). Consider this logic: Is the end time for the SHIFT greater than the start time for ALLOWANCE PERIOD? if yes, the person has worked ...


0

In A2: =HOUR(A1)+MINUTE(A1)/60


0

| Col A | Col B | Col C | +-------+-------+-------+ 1| | 100 | 600 | =A1+B1 + 500 +-------+-------+ 2| | 200 | 700 | =A1+B2 +-------+-------+-------+ In Cell A1, you can add matrix "=transpose({500,500})", then Col B function will work well.


Top 50 recent answers are included