# Tag excel-formula

## [Solved] Identify and output first and last records by group [closed]

If able to sort by date within task and to ensure that any subsequent sheet starts at a new task and the last ColumnB and ColumnC values on each sheet are repeated in the row immediately below, a formula might…

## [Solved] Calculate Gain Loss Excel Percentage [closed]

I reversed it, I made it into an if function, if anyone else is wanting to know how to get a steady Plus/Minus from Gains and Losses this formula worked for me =IF(C4<=B4,1-B4/C4,C4/B4-1) 5 solved Calculate Gain Loss Excel Percentage…

## [Solved] How to add the result of a formula to another in excel?

When you say ‘think of sheet 2 as a db’, then it should have key column to identify a record. The whole point is that sheet2 must also have 3 columns – code, description & price with list of all…

## [Solved] How to do multiple lookup and results in Excel?

=IF(ISERROR(MATCH(A1,SHEET2!A:A,0)),IF(ISERROR(MATCH(A1,SHEET2!B:B,0)),text3,tex2),text1) 1 solved How to do multiple lookup and results in Excel?

## [Solved] If a cell contains mulitple instances of specific text, then extract top 3 of the specified text found in the cell

google-spreadsheet solution: ‘for a CSV of all matches =arrayformula(TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH(E2:E8, A2)), E2:E8, “”))) ‘for a CSV of the first three matches =replace(arrayformula(TEXTJOIN(“, “, TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, “”))), find(“|”, substitute(arrayformula(TEXTJOIN(“, “, TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, “”))), “,”, “|”,…

## [Solved] how to calculate time difference in excel working hours only

You will need a helper column with this formula: =24*(SUMPRODUCT((TEXT(ROW(INDEX(AAA:AAA,\$F\$1):INDEX(AAA:AAA,\$F\$2)),”dddd”)=A1)*(C1-B1))-IF(TEXT(\$F\$1,”dddd”)=A1,MOD(\$F\$1,1)-B1,0)-IF(TEXT(\$F\$2,”dddd”)=A1,C1-MOD(\$F\$2,1),0)) Then sum that column. Here it is in one formula using NETWORKDAYS.INTL =IF(DATEDIF(F1,F2,”d”)>1,NETWORKDAYS.INTL(F1+1,F2-1,”0000011″)*12+NETWORKDAYS.INTL(F1+1,F2-1,”1111101″)*4,0)+IF(DATEDIF(F1,F2,”d”)>0,(MOD(F2,1)-IF(WEEKDAY(F2,2)<6,TIME(7,0,0),TIME(9,0,0)))*24+(IF(WEEKDAY(F1,2)<6,TIME(19,0,0),TIME(13,0,0))-MOD(F1,1))*24,(F2-F1)*24) 4 solved how to calculate time difference in excel working hours only

## [Solved] How to extract all unique values from a column based on multiple criterion in Excel

You would not be able to do this only with excel formulas and you would need a VBA solution. If your Sheet1 contains data like below, and Sheet2, Try this simple VBA code , Sub uniqueList() Dim i As Long,…

## [Solved] Count years of increasing profits

edit: formula for the better explained requirements in comments (see previous versions in history): =COLUMN(I2) – MAX((\$C2:I2 < \$B2:H2) * COLUMN(\$C2:I2), COLUMN(\$B2)) 10 solved Count years of increasing profits

## [Solved] Add lines and duplicate data a set number of times

Try this after renaming the referenced worksheet. Sub expandMonths() ‘ Dim i As Long, j As Long, m As Long, a As Variant With Worksheets(“sheet1”) i = .Cells(.Rows.Count, “A”).End(xlUp).Row Do While i > 1 a = Array(.Cells(i, “A”).Value2, .Cells(i, “B”).Value2,…

## [Solved] How to count the number of authors in a column? [closed]

I’m assuming that you have 10 authors in a column A. The first record in A2 and last in A11. Then You can get the count in A12 by using this formula =COUNTA(A2:A11) 1 solved How to count the number…

## [Solved] Excel Number Separation [closed]

A solution with a formula only: We assume that A1=1-3. Fill the following formula into A2 and copy it down. =IF(A1<>””,IF(ISNUMBER(A1),IF(A1+1<=VALUE(RIGHT(A\$1,LEN(A\$1)-FIND(“-“,A\$1))),A1+1,””),VALUE(LEFT(A\$1,FIND(“-“,A\$1)-1))),””) The result will be 1 2 3 This works for any numbers devided by -. 1 solved Excel Number…

## [Solved] Count number of times value appears consecutively in a row – Excel

Use: =AND(A1=1,B1=1)+COUNTIFS(A1:W1,0,B1:X1,1,C1:Y1,1) We want to count where the there is a pattern of 0,1,1 hence the equal size but offset ranges. It will count where the pattern matches. We also want to count if A1 and B1 both equal 1…

## [Solved] how to calculate XIRR dynamically in excel and in google sheets

Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. “B”, is in J2, array formula**: =XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B\$2:B\$8=J2,{1,1}*ROW(B\$2:B\$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B\$2:B\$8=J2,{1,1}*ROW(B\$2:B\$8)))))),TODAY())) Copy down to give similar results for Funds in J3, J4, etc. I tend to…

## [Solved] Vlookup all the values matching the search term instead of just one.

With Google Sheets use Query: =QUERY(A:A,”select A where A contains “”” & B3 &””””) Since you have the Excel tag use this formula for excel: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(\$A\$2:INDEX(A:A,MATCH(“ZZZ”,A:A)))/(ISNUMBER(SEARCH(\$B\$3,\$A\$2:INDEX(A:A,MATCH(“ZZZ”,A:A))))),ROW(1:1))),””) Copy/drag it down sufficient for your needs. 5 solved Vlookup all the values matching…

## [Solved] Excel Formula to extract previous word (towards left) from a specific position

If there are no spaces between numbers then you can use following formula. =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A3,” yrs”,REPT(” “,99)),99)),” “,REPT(” “,99)),99)) 2 solved Excel Formula to extract previous word (towards left) from a specific position