2

Solution with a Formula : A1 : =ArrayFormula(if( mod(ROW(A1:A100),4)=1, text((round(ROW(A1:A100)/4)*7)+43730,"dddd dd mmm yy") ,) ) Solution with drag and drop Enter the dates in the cells : A1 is the first sunday, A5 is the second with this formula =A1+7 ...


2

The "elegant" way to use FILTER to filter A2:F where B2:B (Power) =I3 and D2:D (Manufacturer) =I2 is =FILTER(A2:F,B2:B=I4,D2:D=I3) The above works because filter allow multiple criteria arguments. Reference FILTER


2

If I remember my highschool days, your expression reads : Sum of the n from 1 to 30. I.E. 1000*1.07^1 + 1000*1.07^2 + 1000*1.07^3 + ... Here's a way to do it : =sum(ArrayFormula(1000*power(1.07,(ROW(A1:A30))))) Result is 101 073.04


1

It was a syntax issue, my data range was incorrect.


1

I added the custom function: function getRangeAddress(namedRange) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(namedRange); return range.getA1Notation(); } and to use it, I did =getRangeAddress(WidgetTypes) and it returns C5:M5 as a string.


1

There isn't a built-in function that returns the address of a named range. One alternative is to use a custom function. The custom function should parse the cell formula to get the name of the named range then the code code could use that string to get the related range and after that you could use getA1Notation. Related How to pass a range into a custom ...


1

It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more). I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...


1

Since you are essentially using an ENUM in Google Sheets and the index is increasing by exactly one in each case, your best bet is to use arrays. If you had a more complicated mapping between integers to strings, you should use the SWITCH function. Here are examples in both directions (Int to String, and String to Int)... Option 1: via arrays (preferred) ...


1

Yes, there is a much simpler way to do what are you trying to do: =INT(AC11/10) Good luck!


1

There is a build-in function for that in Google Sheets. =today() Just write that in a cell of your choice. You only then need to format the cell and display the date as you like by going to Menu--> Format --> Number --> More Formats --> More date and time formats. You can choose and also create your own variations.


1

Following the OP's Edit things are quite clearer. Using the FILTER function =FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar")) Extra tip If you were to use the | (pipe symbol) in the FILTER function which stands for OR, then the formula would change to: =FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar|Energy ...


1

I think this is what you want having worked my way through this jungle: =IF(AND(B4="YES",G4=0),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(AND(C4="NO",F4>10),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(AND(C4="NO",F4>10),ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*35*12),IF(F4<=10,(ROUNDDOWN((AVERAGE(I4,K4,M4,O4,Q4,S4))*25*12)),0)))) Problem ...


1

The formula you need for range A1:A is: =and(A1<>"",counta(B1:J1)<>0)


1

Formula =ArrayFormula(MMULT(TRANSPOSE((ROW(B1:B9)<=TRANSPOSE(ROW(B1:B9)))*ISTEXT(B1:B9)*1),ISTEXT(B1:B9)*1)) What you are looking to do is called "running total". The above formula use arrays, TRANSPOSE, MMULT (matrix multiplication), ISTEXT and unity multiplication (to parse TRUE as 1 and FALSE as 0) instead creating a COUNTA formula chain. The ...


1

There are three ways of matching Unicode characters according to Google Sheets' regular expression documentation: Using exactly two digit hex code: \xA0 Using up to three digits octal code: \240 Using any length of hex: \x{A0} or \x{0A0} or \x{0000000A0} etc. (any reasonable number of leading 0's is allowed apparently) Other ways of specifying characters ...


1

Yes, it can. The formula in the question is very convoluted and there isn't a description of what it does, so here is a a simple formula that returns Saturday Sunday Monday Formula =ArrayFormula(ARRAY_CONSTRAIN(TEXT(A2:A,"dddd"),3,1)) Explanation TEXT(A2:A,"dddd") This returns the day of the week for all cells on A2:A ARRAY_CONSTRAIN is used to limit ...


1

As of September 2019, Google Sheets doesn't support 3D references like 'Mon-D:Sun-D'!$DN$209. One alternative it's to replace the 3D reference by regular (or 2D ) references. In the specific case of SUM, as was shown on the previous answer, add a reference for each of the sheets. Another alternative is to create a custom function by using Google Apps ...


Only top voted, non community-wiki answers of a minimum length are eligible