Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

Filter by
Sorted by
Tagged with
1 vote
1 answer
8 views

How to return a value from a row from the last column a text appears

I am having problems returning the correct value i need. I enter data on this table. And i want to return some values on the other table. In that second table i want to return the last date his name ...
1 vote
1 answer
13 views

Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
0 votes
1 answer
7 views

Google Sheets formula to format substrings of column concatenation?

If I have a spreadsheet with columns for author, year, title, publication, etc, could I add a column that concatenates and formats those as a citation for a bibliography? It would be something like ...
0 votes
1 answer
39 views

How do I advance the column instead of the row when I copy/paste a formula?

So pretty simple thing I am doing, pulling data from a form and putting it in a template. =('Form Responses 1'!B3) is what I am doing, which works fine for my needs, however when I copy and past ...
1 vote
1 answer
2k views

Can automatic send emails when checkbox = True?

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly ...
0 votes
1 answer
793 views

Google Sheets Cell Reference +1

I am wanting to enter a bunch of cell references that are affected by a change in only one of them. On one sheet tab, I have a list of dates in column C, and dollar figures in both D and E. In another ...
0 votes
1 answer
138 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
0 votes
1 answer
24 views

How to make formula based off a withholding table?

I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
1 vote
1 answer
5k views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
0 votes
1 answer
1k views

Using checkbox to copy data form worksheet A to worksheet B

I am trying to use the new Checkbox function in Google Sheets. I can't figure out how If I click the checkbox in A9 on Worksheet 2 I want the Text in A10 and the $ amount in A11 to be copied to cells ...
0 votes
2 answers
1k views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
1 vote
1 answer
41 views

How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
0 votes
1 answer
19 views

How to find the next lowest available number from a list on Google Sheets

I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500 As an example if 1 has been used the next available number will be 2. However, ...
0 votes
2 answers
1k views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
0 votes
1 answer
20 views

Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
1 vote
1 answer
229 views

Google-sheet IF function not working

I need a formula in Google spreadsheet that will: +1 when the value is >=5, +2 when the value is >=10, +3 when the value is >=15, +4 when the value is >=20, +5 when the value is >=25 ...
0 votes
1 answer
80 views

Is it necessary to use ArrayFormula to get the SUMIF function to work properly in Google Sheets?

I am trying to find the sum of values in a column based on the month in an adjacent column. For example, Sheet 1 has Three columns, one for student remark, one for payment date, and one for amount. ...
1 vote
2 answers
22 views

Count and number how many times the same conditions have me

I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) ...
0 votes
1 answer
24 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
0 votes
2 answers
6k views

How to get price of specific cryptocurrencies at specific times?

I have an assortment of cryptocurrency transactions over time. Various currency pairs at various prices. I need to figure out what the price of those currencies was, in Bitcoin, at the time that I ...
0 votes
1 answer
26 views

Calculate specific contents of a cell that contains a string of numbers and text

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, ...
0 votes
1 answer
21 views

Transpose Time Google Sheets

I love the transpose date function above. Can you do the same thing with time in hour increments? If someone checks in at 2pm and checks out at 7pm, the cells to the right would count 2pm, 3pm, 4pm, ...
0 votes
2 answers
2k views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0 votes
1 answer
479 views

How can I do an aggregate function with multiple columns with a Google Sheets query?

I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this: =query(A1:B11, "select avg(A&B)", 1) <- ...
2 votes
2 answers
3k views

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
0 votes
1 answer
63 views

How to show blank cells instead of 0s in Google Sheets?

I have 4 empty cells, and a formula in 4 other cells that show data from those cells. The formulas look like this - =IF(B5 > C5, B5, C5) =IF(B5 > C5, C5, B5) ==IF(B7 > C7, B7, C7) =IF(B7 >...
0 votes
1 answer
31 views

Query function with variable in the Col1=

Here' the formula: =query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
1 vote
1 answer
257 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
1 vote
0 answers
12 views

Why 2 columns of the same value give different total?

Sample Data in Result Sheet I have a formula in A2 that extracts data & splits it into unit & product (A & B). I used the SUMIF formula to give the the total of each separate set of ...
1 vote
1 answer
30 views

Highlighting a single cell when partial text shows up in a range of other cells

I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to ...
1 vote
1 answer
74 views

sumif not working sometimes

I have read similar questions but unfortunately didn't help me. Here is a copy (with sensitive info redacted) of my data. Google Sheet In column U and V I am trying to sum columns D to O if T is "...
0 votes
1 answer
19 views

Why is COUNTIF returning a 0? It should be returning a value of 8

I have a fairly simple countif function that counts how many items are 90 days old or more. =COUNTIF(PROCFilter!H2:H,">=90") But all this is returning is a value of 0, when I know that ...
0 votes
1 answer
2k views

Google Sheets function returns an incorrect value

I have a very simple function for every cell in column D, for example: D24 =C24-(B24*N$2) Example with values: D24 =10-(1*6,5) This works; however, not all the time. For cell D24 this returns a ...
0 votes
1 answer
22 views

Is there a way to return more than one row from multiple matches in Google Sheets?

I'm trying to find a way to fill two columns in one table with two rows of data from another table based on matched criteria. To be more specific, A3:D contains the data I'd like to pull from. I'm ...
2 votes
1 answer
254 views

Text result of JOIN is longer than the limit of 50000 characters

Sample Sheet I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula. I've got the JOIN formula set up for that and it works ...
0 votes
2 answers
101 views

Cell reference for contains value in Google Sheet

Looking for help referencing another spreadsheet, where I want to lookup several values in cell A2, to see if the value is contained in the cell (as there are variations), then setup an else clause at ...
0 votes
1 answer
40 views

How to sum cells that are in hexadecimal format

First of all, read 32, 1023 with the 0x32 and 0x1023 to identify that it is a hexadecimal number. When we only have numbers the sum is done with decimals and it seems to work. The problem is that in ...
0 votes
2 answers
34 views

How do I automatically record finish position?

I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, ...
0 votes
1 answer
42 views

Google Sheets: Making a function to find a number and impute it once

I am trying to make it possible to find the highest number under 30, and than place it in another cell. Then that cell will not change when another number becomes higher. For example if the highest ...
0 votes
1 answer
36 views

Allocate an amount in one cell amongst or between several cells based on the value of each of the several cells

Here is the link that relates to my question: https://docs.google.com/spreadsheets/d/1dnWuREv3Rf2EWyDQDnyHyZWZ8ItbyKt_EfxPQwWhTnA/edit?usp=sharing Cell C13 is a Retirement Annuity contribution of my ...
0 votes
3 answers
111 views

Average formula for Google Sheets with substitution over some figures

I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1. Thanks
0 votes
2 answers
32 views

Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
15 votes
5 answers
36k views

Perform basic maths on contents of Google Document (not spreadsheet)

In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this ...
3 votes
3 answers
411 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
0 votes
3 answers
79 views

Google Sheets: keep track of days spent in different countries

I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country: Date & time From To Outbound country Inbound country Days in ...
0 votes
1 answer
152 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
0 votes
1 answer
23 views

How to write multiple IF statements in 1 cell based on multiple dropdown choices?

L16-L19 are Yes/NO dropdowns How can I use multiple IF statements in L20? For example: yes yes yes no, “proceed” No no yes yes, “don't proceed” Yes no yes yes, “cannot move forward”
1 vote
1 answer
29 views

How to assign cell values based on options from another list

I am sure there is a simple formula for this but I have not been able to find a solution anywhere. I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a ...
0 votes
2 answers
1k views

Import Range of cells as per today date in another sheet

There are three columns A, B, C AND today date is in the first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet. ...

1
2 3 4 5
37