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 suit: =IF(A1<>A2,A1&”|”&VLOOKUP(A1,A:B,2,0)&”|”&C1&”|”&B2-VLOOKUP(A1,A:B,2,0),””) with the columns where the formula output is evident filtered to exclude blanks, … Read more
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 [closed]
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 possible codes to be entered in sheet1. Then in Sheet1 in B2 enter =VLOOKUP($A2,Sheet2!A:C,2,0) Then … Read more
=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?
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, “”))), “,”, “|”, 3)&”|||”), len(A3), “”) excel-2016textjoin solution: ‘for a CSV of all matches input as array formula … Read more
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
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, j As Long, str As String For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row For j … Read more
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
Try this after renaming the referenced worksheet. Sub expandMonths() ‘https://stackoverflow.com/questions/52304181 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, 0, 0, 0, 0) m = .Cells(i, “C”).Value2 j = Application.Match(.Cells(i, “A”).Value2, .Columns(“A”), 0) If … Read more
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 of authors in a column? [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 Separation [closed]
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 because it should be positive but does not meet the pattern. 5 solved Count number … Read more
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 prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), … Read more
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 the search term instead of just one.
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