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
1answer
15 views

How to show Day of Week in Order by Day of Week in Google Sheets

I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K Example J contains 1/1/2020 ... until 9/29/2020 K contains numeric value 123 ... 1999 =...
2
votes
2answers
2k views

Escape single quote in cell content in a QUERY

My Problem I have a Google Spreadsheet with two tabs - one containing regexes and values: And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
1
vote
2answers
48 views

Count how many times a certain word comes before and after another in a list

I am trying to evaluate some information from an animal studying exercise using Google Sheets. I'd like to know how many times the animal: forages then allogrooms sleeps then allogrooms etc. Also ...
1
vote
1answer
5k views

If cell contains then

I need to create a formula that will check for a word in a cell and return a value or if the word does not exist then check for another word. Eg. Check Cell A1 contains the word "red" then print "...
0
votes
1answer
52 views

The cell is filled with $0,00 but IF(Cell >0;True;False) return True

In Google Sheets, when I compare a Cell filled with $0,00 using: CELL [$0,00] IF(Cell > 0; true; false). It returns true, but Cell is = 0. It should return false. It seems to be related to ...
0
votes
2answers
1k views

Applying IMPORTRANGE formula in Google Sheets between tabs

I have one Google sheet having three tabs, Sheet 1, Sheet 2, Sheet 3. I have another tab having the name "Sheet 4" in which I want to import all data from these three tabs, but the problem is data in ...
2
votes
1answer
24 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
0
votes
1answer
1k views

How to use calculated fields to pull up “Grand Total” in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
2
votes
1answer
26 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
0
votes
1answer
749 views

Auto-populate cells based on previous cell values in Google Sheets

I have two columns with information. Values in Column B have been manually added to match the values in Column A. Is it possible to have a function that auto-populates column B with the right values ...
0
votes
2answers
118 views

A formula for importing specific data into another sheet

I need a function for column K of Sheet1, that says: "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2" ...
19
votes
3answers
17k views

Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
1
vote
1answer
20 views

How to copy and paste a formula changing only the cell references made on this sheet

How can I copy and paste a formula changing only the cell references made on the active sheet and leave references to ranges on another sheet as is?
0
votes
1answer
28 views

How to get the SUM total value to appear in cell vs the formula?

So I have created a spreadsheet to add cells =SUM(B2:B20) ... When I hit enter the sum value should be in the cell I created the formula but instead, I have just the formula after hitting enter. How ...
0
votes
1answer
293 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
0
votes
1answer
24 views

Any way to duplicate a formula that needs individual relatively absolute references?

I need to duplicate a formula down for many rows. However each one needs to be an absolute reference, referencing a specific cell in that individual row. I can't just fill down the original formula ...
0
votes
2answers
1k views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
1answer
269 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
1
vote
1answer
20 views

How to stop IFS function always giving the same result for x even if greater than x or less than x

C9 being the group size, these are the formulas I used: =IFS(C9<5, "0%",C9>=5,"15%", C9>=15, "25%", C9>=40, "40%") =IF(C9<5,"0%",IF(C9>=5,"15%",IF(C9>=15,"25%",IF(C9>=40, "...
0
votes
1answer
517 views

Google Sheets: Fill out dates and replace blank cells with zeroes

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable: I think there are two reasons for this: Not all dates have values associated with them, and ...
0
votes
1answer
22 views

Adding Positive Values Only (across multiple sheets)

The example formula below works perfectly in my spreadsheet if ALL the numbers are positive. However, I would like to pull the values from the cells indicated below (from 14 different pages), but ...
0
votes
1answer
49 views

If cell y contains any word from range x:x, mark cell y green

In Google Sheets, I have three columns (with different lengths!) Column1 contains firm names (50) Column2 containts firm names (100) I would like to mark a cell in Column1 green (or with a number in ...
0
votes
1answer
53 views

How to use arrayformula to find and get data from only a certain, dynamic set of columns?

I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order. In the example below I've got the header values: AA - BB - CC - ...
0
votes
2answers
42 views

How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
1answer
37 views

How to Copy Cell Value and Format From One Tab to Another Using a Formula?

An answer was provided in this question: webapps: Cell reference with colour formatting for doing so within a tab, but not across tabs. Does anyone have suggestions for a script that will work across ...
0
votes
1answer
25 views
1
vote
1answer
19 views

How to create a link with content bound to other cell's content in Google sheets?

As stated in the tittle I'm trying to make the content of a cell that links to an other cell have it's display text be based on the other cell's content. Before I was able to do it by right clicking ...
0
votes
1answer
32 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 ...
-1
votes
3answers
29 views

Summing every other row depending on the classification of the row above

I've got a confusing question, I can't figure it out. I have this spreadsheet of states population and their political control, each on separate alternating rows (political control is designated with ...
0
votes
2answers
792 views

Conditionally format cells that not match with range from Sheet2

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my jira. Now I am trying to compare Column F from sheet1 and sheet2 to confirm if they are ...
0
votes
2answers
48 views

In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly. So if the value in a col A cell = apple, ...
0
votes
1answer
23 views

How to assign country based off list of either provinces or states?

I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations. I also have a list of ...
-1
votes
1answer
41 views

Google Sheets: carry value from one sheet to another

I have 2 worksheets in 1 file. I want to bring the value from 1 sheet to another. I only want the value IF it has the name of someone. Sheet 1 +---+-------+--------------+ | | A | B | ...
0
votes
1answer
13 views

Google Sheet average a row from last 10 days to the current date

I am looking for a formula that would automatically average their own row on C3 to C10 based on the last past 10 days from the current day as seen on their right side. The list doesn't have the ...
0
votes
1answer
2k views

How to import data from Investing.com into spreadsheet?

I want to import the live individual stock price in the Google Sheets as highlighted on the image. Is that possible? https://in.investing.com/equities/jagson-airlines-bo
1
vote
1answer
15 views

importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "...
1
vote
1answer
23 views

Is there a formula to automatically populate invoice with only the products that have been ordered and excludes the products not ordered?

I have a range of products in my order data sheet and I need help creating a formula that automatically fills up my Invoice sheet with only the products that were ordered. Currently I can only ...
0
votes
0answers
28 views

How to write a conditioning rule referencing another cell in Google sheets?

Problem: I want the color of the cell to change based on the value of another cell. I have a cell (B1) with MTD capture that is calculated by taking asset A and dividing it by Asset B in different ...
-1
votes
0answers
17 views

Actual time and highlighting cells [duplicate]

Using Google Sheets I've created a schedule and I'd like to highlight a cell whenever a specific time applies. https://docs.google.com/spreadsheets/d/1Onw2ORGB5j8s4l5VmdJp354NJWcqGOV-AzWryoGrFq0/edit#...
0
votes
1answer
480 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
0
votes
1answer
71 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
0
votes
1answer
54 views

Need to create a formula with the operand in a different cell in Google Sheets

For eg . Existing formula which works Query(A4:K,"Select A Where I = > 0.3") should be replaced by Query(A4:K,"Select A Where I = "&N1&"") with cell N1 containing value '>0.3' Appreciate ...
3
votes
1answer
2k views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
0
votes
1answer
70 views

Google Sheets MINUS formula is adding

I have been working with some numbers in Google Spreadsheets and when I type the formula =MINUS(A1,A2) or even =A1-A2 the spreadsheet keeps adding the two numbers instead of subtracting them. What am ...
1
vote
1answer
5k views

QUERY error NO_COLUMN:col1 [duplicate]

=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"), "select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53 where col53<> ...
1
vote
3answers
185 views

Google Sheets issue with literal constants in functions [duplicate]

When attempting to use any literal strings in formulas I get a Formula parse error and #ERROR! in the column with the formula. For example: =IF(D43=“x”,MULTIPLY(E42,2)). I’ve been told that ...
1
vote
1answer
69 views

How to stack ranges horizontally?

If {range1; range2; range3} (I believe this is called embedded arrays) outputs the combined data in a column - top to down, what puts them side by side next to each other? The 3 ranges may or may ...
-1
votes
1answer
1k views

Google Sheets IMPORTHTML parse error

I want to use the IMPORTHTML function of Google Spreadsheets. There is this example by Google itself: =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4) Unfortunately (like ...
0
votes
1answer
47 views

Enter Tab Name In Cell To Calculate Sum of Column or Range

I have seen one other similar question on this topic posted a few years ago, but I have been unable to have any success with the suggestions given there. I have sales sheets (a new one added each day)...
0
votes
1answer
42 views

spreadsheet conditional: if in top x then

spreadsheet data: Total Average Highest Top Sales $23,550.00 $7,850.00 $8,100.00 not $42,700.00 $14,233.33 $14,400.00 not $30,500.00 $10,166.67 $10,300.00 not $37,840.00 $12,613.33 ...

1
2 3 4 5
26