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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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,...

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

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

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

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

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

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

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 =...

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

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

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

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

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,excel-formula,formulas,array-formulas

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

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

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

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