Google-sheets – How to increase google sheets cell number by adding 20 in a formular

google sheets

I would like to use "+" or "- " in referencing a cell eg

=i(10+23) = i33 .

So that I don't have to increment the cell reference manually .

Any help would be great ?

Best Answer

You can use the INDEX function, combined with ROW() and COLUMN(). Here are two simple examples:

Left: =INDEX(A:A, 5) returns the contents of the fifth cell in column A.
Right: =INDEX(A:A, ROW()+5) returns the contents of the cell five rows down in column A. All cells in italic use the same formula.

Formula

=ArrayFormula(IF(WEEKDAY(FILTER(C:C,C:C>0),2)=1,WEEKNUM(C:C,1),))

Explanation

WEEKNUM(C:C,1) returns the number of the week for all the values column C. To to fill only the cells that corresponds to mondays and that row is not empty, it was included as the TRUE result of an IF function with the following test:

WEEKDAY(FILTER(C:C,C:C>0),2)=1

ArrayFormula makes that the results be expanded to the required rows.

Google-sheets – How to change many formulas at once in Google Sheets

If I understand your question, I think a regular expression search might do the trick. In the replace dialog, check the box for regular expression and search within formulas.

Try searching for: (Count.\*)\\).*

And replace with: if($1 = 0), "", $1)/7)

I think that should do it.

Best Answer

Related Solutions

Google-sheets – Increase 1 increment every x rows on Google Sheets

Formula

Explanation

Google-sheets – How to change many formulas at once in Google Sheets

Related Topic