Stack Overflow
Stack Overflow

Questions tagged [formulas]

A generic tag for questions concerning spreadsheet formulas. Use only when there is no more specific tag, such as [excel-formula] or [lotus-formula].

In a , formulas allow users to populate spreadsheet cells with data calculated by some mathematical, statistical, lookup or text-manipulating formula, often using references to the values of other cells. Formulas will automatically be re-evaulated when a referenced cell changes, allowing users to see how changes in inputs affect the value of the formula. This also creates the possibility of feeding more complex input into other formulas or charts (by referencing one or more cells whose values are set by formulas).

735 questions
143
votes
9 answers

Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! How I remove header? Can I?
Poliane Brito
  • 2,142
  • 3
  • 18
  • 26
44
votes
7 answers

Timezone conversion in a Google spreadsheet

I know this looks simple. In a Google spreadsheet, I have a column where I enter time in one timezone (GMT) And another column should automatically get time in another time zone(Pacific Time) GMT | PT ----------|------------ 5:00 AM | 9:00…
SangyK
  • 799
  • 2
  • 6
  • 16
31
votes
5 answers

Getting formula of another cell in target cell

How does one cell obtain the formula of another cell as text without using VBA? I can see this question has already been asked many times and the answer is always to write a custom function in VBA. However, I found a post made in 2006 which claimed…
Pupper
  • 2,315
  • 2
  • 22
  • 29
29
votes
3 answers

How to use native spreadsheet functions in google apps script?

Is it possible to use native spreadsheet functions such as sum() or today() in google apps script, when writing code for google Spreadsheet? If so, how do I do it?
user3347814
  • 1,138
  • 9
  • 28
  • 50
21
votes
1 answer

Linking one google docs spreadsheet to another

I have data in one google docs. Call it docA. I would like to reference this data in docB. Something along the lines of =docA!Sheet1!A2 How would I be able to go about this? I do not mind changing security settings and the like
mhopkins321
  • 2,993
  • 15
  • 56
  • 83
20
votes
7 answers

HTML to Excel: How can tell Excel to treat columns as numbers?

I need to achieve the following when opening an HTML in Excel (Response.contentType="application/vnd.ms-excel") : force Excel to consider content of td cells as numbers make the above so that any subsequent user-entered formulas work on these cells…
Wheelwright
12
votes
1 answer

In Google Sheets, How do I find the row number of a cell with certain text in it?

In Google Sheets, What do I do if I want a cell to display the row number in which a certain word/line of text appears? So, I'm working with two kinds of spreadsheets here: One has the number of "Competitors" (which I'm looking for) in the 7th row…
Maxwell Ryan Fuller
  • 475
  • 1
  • 4
  • 11
12
votes
3 answers

How do I reference the cell to the left of the current cell in Google sheets?

I have to apply a function to every cell in column 2, which retrieves information about a symbol in column 1. I have 100 columns and don't want to individually plug each symbol in, so I need a way to reference the information in A2 in the function…
Swogget
  • 185
  • 1
  • 2
  • 9
10
votes
3 answers

How to find the first cell in a row where value is not empty and check if the number is less or equal the number in other cell

I've got the following Google spreadsheet: item have ready need1 need2 need3 A 1 2 1 B 1 2 1 1 C 2 2 etc I want to fill ready column as follows: find the first column in…
timbre timbre
  • 12,648
  • 10
  • 46
  • 77
9
votes
2 answers

In Google Sheets, how can you find the mode of non-numerical data?

If I want to find the mode of A2:A10, but my values are something like: Foo Foo Bar Foo Baz Bar Foo Bar Foo , what can I do? The =MODE() function only takes numerical data, so is there a way to find the most frequent of these values? Thank you!
AAM111
  • 1,178
  • 3
  • 19
  • 39
9
votes
3 answers

Insert a Newline in Excel Formula (MacOS)

I am using MS Excel on a Mac. I have a formula, and I wish to insert a newline into it: Guests!A1 & " " & Guests!B1 & [newline here] Guests!C1 How can I do this?
Xofo
  • 91
  • 1
  • 1
  • 2
8
votes
3 answers

How can I normalize / asciify Unicode characters in Google Sheets?

I'm trying to write a formula for Google Sheets which will convert Unicode characters with diacritics to their plain ASCII equivalents. I see that Google uses RE2 in its "REGEXREPLACE" function. And I see that RE2 offers Unicode character classes. I…
Kirkman14
  • 1,506
  • 4
  • 16
  • 30
8
votes
2 answers

Multiple formulae with shared parameters in R

We're trying to come up with a way for an R function to handle a model which has multiple responses, multiple explanatory variables, and possibly shared parameters between the responses. For example: Y1 ~ X1 + X2 + X3 Y2 ~ X3 + X4 specifies two…
Spacedman
  • 92,590
  • 12
  • 140
  • 224
7
votes
1 answer

Select entire row as range (indefinite number of columns)

I'm a beginner at Google Apps Script and want to select an entire row after a certain cell to use in a formula. function setUpSheet() { var infoRange = SpreadsheetApp.getActiveSpreadsheet().getRange("A1:D1"); infoRange.setValues([ ["Last",…
swimminggymnast
  • 81
  • 1
  • 2
7
votes
1 answer

How to calculate Date Time Difference in LibreOffice Calc

I need to calculate the difference between two Date Time fields in LibreOffice Calc, with the answer in hours, positive or negative. When there is a positive result =J2-I2 works, and returns answer in hours. When there should be a negative result…
CedarSage
  • 73
  • 1
  • 1
  • 3
1
2 3
…
48 49