If start and end "timestamps" will always be within working hours then you can use a formula like this: =(SUM(NETWORKDAYS.INTL(A2,B2,{1,"1111101"},H$2:H$10)*{12,8})-IF(WEEKDAY(B2)=7,8,12))/24+MOD(B2,1)-MOD(A2,1) Assuming start date/time in A2, end date/time in B2 and holiday list in H2:H10 Format result cell as [h]:mm and if start time is today (Monday) at 14:00 and end...

google-spreadsheet,spreadsheet,worksheet-function,formulas

If you want to use OR in Arrayformula you'll need to use +. Can you see if this works ? =ArrayFormula(IF(LEN(H4:H), IF((O4:O = "Yes")+(H4:H = "Yes"),"Yes","No"),)) ...

google-spreadsheet,spreadsheet,formulas

Take a look at this example sheet where I used this formula to generate the desired output =query(ArrayFormula(if(len(query({filter(Sheet1!A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3"))=0, 0, (query({filter(A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3")))), "select Col1, (Col2+Col3)*1, Col2, Col3, (Col2/(Col2+Col3))*100...

excel,excel-formula,excel-2007,formula,formulas

this should work: =INDEX(D1:D10,MATCH(NOW()-TODAY(),E1:E10,1)) NOW()-TODAY() gets the current date, MATCH find the row of the first value that is smaller or equal than the value and index returns the name in the corresponding row....

google-apps-script,google-spreadsheet,google-form,formulas

Assuming your data starts in row 2, try: =ArrayFormula(iferror(vlookup(unique(B2:B), sort({B2:B, A2:A}, 2, 0), {1, 2}, 0 ))) Change ranges to suit. Don't forget to include the sheetname if you need that formula to appear on another sheet....

Do like this: public int formulaMethod(int n) { int result = 1; for (int i = 1; i <= n; i++) { result *= i; } return result; } Hope it helps...

formatting,openoffice-calc,formulas,libreoffice-calc

You can increase shown numbers after the decimal point by this: Tools -> Options -> OpenOffice.org Calc -> Calculate Set flag "Limit decimals for general number format" and set maximum number in input box (for me it's 20).

If you want to disregard the numbers within the Text pseudo-field, you will have to parse the split-out value closely for characters within ASCII 48-57. That ugly formula in B1 is, =TRIM(LEFT(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0, MIN(INDEX(ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0)))+((CODE(MID((MID(A1,...

Try this formula =LOOKUP(2,1/SUBTOTAL(3,OFFSET(A$1,ROW(A$1:A3)-ROW(A$1),0)),A$1:A3)-B4 The SUBTOTAL/OFFSET part returns a 1 or zero for each value in column A depending on whether it's visible or not, LOOKUP finds the last 1 (equivalent to the last visible value) and gives that value....

You need to say type t = bexp You just left out the type name t....

I don't understand your requirement but your formula is full of syntax errors so the following might at least help: =IF(AND(G11="",H11=""),"",IF(AND(G11>=0,H11=""),G11-E$1,IF(AND(G11>=0,H11>0),"paid"))) ...

Just answered a similar question here. The workaround is to use INDIRECT() with ROW() =IF(INDIRECT("'Form Responses 1'!B"&Row(A16)) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Bird","4")))) Or =IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Cat","2",IF(INDIRECT("'Form Responses...

excel,vba,excel-vba,lookup,formulas

Imho this is a classic case of "use a database instead of Excel", especially if you want to make these kind of queries regularly. However, something like this should achieve what you want in VBA: Dim customer As String Dim region As String Dim price as Double For r =...

I fixed it by creating a total for each column in the footer and then adding those together in the grand total field. Finally I hid those labels so they wouldn't be seen.

Calculated column formula for Quarter field could be something like: =IF(OR(Month="January", Month="February", Month="March"), "Quarter 1", IF(OR(Month="April", Month="May", Month="June"), "Quarter 2", IF(OR(Month="July", Month="August", Month="September"), "Quarter 3", IF(OR(Month="October", Month="November", Month="December"), "Quarter 4", "")))) This makes sure that you have a valid month or the formula leaves the Quarter field blank....

Call the doCalcTwo function at the end of doCalcOne, passing along any variables you need, and you should get what you expect! You can nuke the 2nd button you have as well. Here's the updated code: Update: I made it all one function and added what I think is the...

Put this in J4 on the 2,800 record set and drag down =IF(COUNTIF(BB$1:BB$20001,I4)>0,TRUE,FALSE) ...

validation,ms-access,input,ms-access-2010,formulas

For a Validation Rule in table design for a field named NoOfDays, I think this does what you want: (Int([NoOfDays]) Or Int([NoOfDays])+0.5) And Between 0.5 And 180 ...

If your goal is to get the percentage of ratings that are (for example) 1-star, then just divide star1 by the number of ratings (which I guess is tot_stars, although the name seems to imply otherwise). That gets you a proportion; multiply by 100 to get a percentage. So, for...

I may be missing out on something, but can't this be accomplished by simply using open-ended ranges ? =sumif(B2:B, "CC/*", A2:A) ...

excel,excel-formula,formulas,array-formulas

Try entering the formula as an array formula (Ctrl+Shift+Enter), that should work.

vba,excel-vba,excel-formula,formulas

The "prompt" parameter of the MsgBox function has a maximum length approximately 1024 characters, depending on the width of the characters used. (see https://msdn.microsoft.com/en-us/library/office/gg251821(v=office.15).aspx) You can obtain the entire formula by printing it in the Visual Basic immediate window with this code: Sub Get_Formula() Rem Opens Immediate Window and Clears...

excel,dynamic,reference,formulas

If the dropdown menu is a Form Control/ComboBox then its linked cell will contain a value 1,2, etc. In another cell you would use INDEX() to translate the selected value to the text Worksheet1, Worksheet2, etc.. All your formulas would then be: =INDIRECT(C1&"!A1") where C1 contains the text Worksheet1 (or...

google-spreadsheet,excel-formula,formulas

In a google spreadsheet, you could try: =query(if({1,1}, ArrayFormula(transpose(split(concatenate(A1:F&char(9)), char(9))))), "select Col1, Count(Col2) group by Col1 order by Count(Col2) desc") Example spreadsheet (formula in J1)...

For a simple function a built-in Excel formula is probably faster since they are most certainly written in C/C++ which produce fast native code. VBA code target the .NET environment, and must be parsed and compiled before it is run, which add some overhead. But for complex nested formulae, since...

excel,vba,logical-operators,formulas,nested-if

Here is the formula according to your discription: =IF(ISNUMBER(SEARCH("No",J2)),IF(E2>=DATE(2014,4,10),"Pass",IF(OR(AND(ISNUMBER(SEARCH("Cancel",G2)),ISNUMBER(SEARCH("No",K2))),AND(ISNUMBER(SEARCH("Void",G2)),ISNUMBER(SEARCH("Yes",K2)))),"Fail","Pass")),"N/A") or if you'd like, you can use this UDF: Function formulasMakeMyHeadHurt(J2 As String, E2 As String, _ G2 As String, K2 As String, _ givenDate As Date) As String Dim res As String If J2 Like "*No*" Then If CDate(E2) >=...

Renewal - there is an instance of the same product and company with an earlier start date =COUNTIFS(A$2:A$7,A2,B$2:B$7,B2,D$2:D$7,"<"&D2)>0 New to product - there is no instance of the same product & company with an earlier start date, but there is an instance of another product & same company with an...

excel,search,nested,excel-formula,formulas

I think this would solve it =IF(AND(IFERROR(SEARCH("string0",A2),0)>0,IFERROR(SEARCH("string1",C2),0)>0), "ST1", IF(AND(IFERROR(SEARCH("string0",A2),0)>0,IFERROR(SEARCH("string2",C2),0)>0), "ST2", A2)) ...

google-spreadsheet,spreadsheet,worksheet-function,formulas

If you had your base date in A1, the following formula would be good for the first day of the previous month: =date(year(A1),month(A1)-1,1) It's even simpler to find the last day of the previous month: =eomonth(A1,-1) ...

By "error" I presume you mean an Excel formula error value, such as #NUM!, #VALUE, #NAME?, etc? Unfortunately, there is no property you can check to determine whether a workbook contains one or more of these after a recalculation. About the only way to do this would be to manually...

Here is an example spreadsheet of Database functions: Example Spreadsheet To exclude zero values, use a second criteria with DAverage. The criteria are designated in a range of cells with column titles. Range A19:B20 is the criteria definition. The formula is: =DAVERAGE(A6:B16,"Bounce Rate",A19:B20) Note that this is matching everything that...

From Query Language Syntax: All values of a column will have a data type that matches the column type, or a null value. Your problem is that Column H has mixed types, number and string....

Excel sets the calculation mode from the first workbook opened in a session, and does not change when you open another workbook that was saved using a different calculation mode. You can change the calculation mode subsequently within the excel session using the Calculation Options or by VBA. So -...

Three cells would be about my limit on stacking conditions within an AND function. Any more and I would perform a conditional count (COUNTIF function) and compare the number. =IF(COUNTIF(A1:A3, "Yes")=3, "Working", "Not Working") Alternate: =LOOKUP(COUNTIF(A1:A3, "Yes"), {0,1,2,3}, {"Not Working","Not Working","Sort of Working","Working"}) =IFERROR(CHOOSE(COUNTIF(A1:A3, "yes")-1, "Sort of Working","Working"), "Not Working")...