Questions tagged [formulas]

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

0
votes
1answer
899 views

Countif based on multiple criteria

For reference here's the sheet: https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing I am trying to create a filter that shows me all the data for May ...
1
vote
1answer
56 views

How can I pull a value from one column based on the values of another column?

When the value in the "Amount Owed" column becomes less than 0, I want the value in the "Year" column to appear under "Year Paid Off." For example, if the "Amount Owed" dropped below zero at "Year" 1,...
2
votes
2answers
914 views

Appending text to each column in Google Sheets

I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example: abcde fghij klmno will all be updated to: abcde-banner.jpg fghij-...
-2
votes
1answer
16 views

How do I find latest invoice number & dates?

Trying to find a formula for the latest date and invoice number. Here is my sheet: https://docs.google.com/spreadsheets/d/1T_rFYDCQhqfrsU9claYPHQTrEz68zpUFKG9HZqqV4Z8/edit?usp=sharing
1
vote
1answer
11 views

How to refer to elements of a dynamic array in Google Sheets?

I'm trying to create teaching timetables dynamically, by searching through a large table with all of the information about what time a lesson takes place, who the tutor is, what age group is being ...
1
vote
1answer
12 views

ArrayFormula not showing 0s

I use the following formula to calculate plain text so to show leading zeros. =ARRAYFORMULA(SUM(S2:S17+0)) but it's only displaying the results without the leading zero. Example, if It is adding up ...
1
vote
1answer
8 views

How to aggregate data from multiple Google sheets with a tag

I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet. Then I am aggregating those three sheets into one and also filter them using a query. Ex: =...
0
votes
1answer
9 views

Partial cell formatting with CONCATENATE in Google Sheets

Is there a way to format partially using CONCATENATE in Google Sheets? For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. I ...
0
votes
2answers
14 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
1
vote
1answer
17 views

Split cells not importing first 0

I am using split formula: =IFERROR(SPLIT(REGEXREPLACE(" , "&U4, "(\d)", "$1 ,"), ", ")) It works great except if I have 0250, it imports it across as 250, even with the cell it's importing ...
0
votes
1answer
13 views

Sort sentences based on formatting

I have a list of sentences in Google Sheets, each word in a different column. There is a stupid man. How we came here we don't know anything about. Finally, it has come to this. Shaking my head. How ...
0
votes
2answers
30 views

Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
1
vote
1answer
11 views

Using SUM in Google Sheets with indirect ranges

I need to use SUM formula to fill the Total formula until next Total comes in Google Sheets like this: Total 6 A 1 B 2 C 3 Total 15 A 9 B 6
0
votes
2answers
45 views

Copying cell values to AVERAGE function

I’m pulling Free Cash Flow Data from Morningstar to Google Spreadsheet. I have formula: =IMPORTREGEX("http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t="&B4,"Free ...
0
votes
1answer
10 views

How to get 15 mins high low data from Google Finance?

I would like to retrieve 15 mins High Low data from Google Finance to Google Sheets. But unable to get any formula. Please guide me.
0
votes
1answer
17 views

Conditional formatting formulas in Google Sheets

I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later". ex: If cell B20 contains "later", then I want A20 to ...
1
vote
2answers
17 views

Conditional Highlight: color change cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column A to be highlighted only once there is a value (name) in the corresponding cells in columns B:D. In this example, I would like ...
1
vote
1answer
15 views

Total of cells formula for numbers as text

I want to total up the numbers of split cells. I have an example as follows: 1234 0000 5678 each number is in its own cell, I want to total it all up underneath to get 6912, so 1234 + 0000 + 5678 = ...
0
votes
1answer
32 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
0
votes
1answer
12 views

How to nullify empty output errors in Google Sheets?

I am using the following code in many cells of the sheet, =SPLIT(REGEXREPLACE("" & S2, "(\d)", "$1,"), ",") not all are going to contain data at any one time. How do I stop the empty cell value ...
0
votes
1answer
9 views

COUNTA and FILTER returning a 1 when it should return a 0

I am using a summary page to show stats from a registration page. I want to show the number of registrations based on their date of registration (which is in column A of the Registrations page. I ...
1
vote
2answers
69 views

How can I allow results from a Google Sheets 'filter' action to be mutable?

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error. ...
1
vote
2answers
1k views

How can I set the increment value for Google Spreadsheets to auto-increment?

I have a series of cells referencing other cells: | Row | a | b | col B shows | |-------------------------------| | 1 | 1 | =a2 | 2 | | 2 | 2 | =a4 | 4 | | 3 | ...
0
votes
1answer
12 views

Get translation using Google Sheets and IMPORTXML

I want to scrap the phonetic guide for a translation in Google Translator using Google Sheets. For instance, if you enter the page: https://translate.google.com/#view=home&op=translate&sl=...
0
votes
1answer
8 views

Split function doesn't get the result that I expect

A1 value is 485065/1, 2, 3 and I expect 485065 and 1, 2, 3 but it comes out 485065 and 1, 2, 2003. =split(a1,"/",false) Please help me why it does.
1
vote
2answers
27 views

Importrange when copied to other cells automatically change range

I am using importrange to pull data from my responses page to my spreadsheet. I have completed the first sheet but I now need to go into the sheet to follow on where sheet one has run out of space. ...
1
vote
1answer
9 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
-1
votes
2answers
39 views

Change a cell to a colour when a tick box is checked in another cell

I have a cell with a checkbox. I want another cell which contains a date to go green when the tick box is checked. I presume this is done in conditional formatting but I am struggling with it.
0
votes
2answers
7 views

Using IF functions with formatting in Google Sheets

I'm working on a Google Sheet and want to reformat all instances where the number in a cell is "2" and the color of the cell is pink. Is it possible to use information about cell formatting (here, e.g....
2
votes
1answer
17 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
1
vote
1answer
37 views

How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example: word Word WOrd WORd WORD wOrd woRd worD without any length word? The given word would go on Row A and the ...
31
votes
2answers
56k views

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results ...
1
vote
1answer
26 views

Automatic merging of duplicate cells under common ID

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
0
votes
1answer
28 views

How to output end task time base on start time, duration and working time in Google Sheets

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example: Working Time: 8:00 ~ 17:00 Lunch Break: 12:00 ~ 13:00 +- Start Time -+- Duration -+- End ...
1
vote
1answer
13 views

I am looking to fetch a table from a website in Google Sheets

I want the first table on this URL https://www.cashbackmonitor.com/cashback-store/amazon/ in my google sheet. When I put this function (below), I can retrieve the first column of the first table e.g....
0
votes
1answer
9 views

Google Sheet Query returns first 3 rows in one row

I have made a simple query that combines data from 4 separate sheets: =query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ") The objective is to ...
0
votes
1answer
18 views

Graphing two lines in Google Sheets based on values in first column

I'm trying to create two lines on the same graph, with one being 'Line A' based on Data labeled 'A' and 'Line B' based on B. The X-axis would be the date and would expand over time as more entries ...
1
vote
1answer
15 views

Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
2
votes
2answers
60 views

Combining numbers based on a separate cell

I'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that Reviews each incoming data set Adds an amount [what ...
1
vote
1answer
17 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
0
votes
2answers
20 views

How to use IMPORTRANGE when I have multiple tabs?

I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
0
votes
1answer
16 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
0
votes
2answers
23 views

Empty cell reference when “Loading…”

I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is a loading time and this ensures that the cells are empty during loading. Because my formula ...
0
votes
1answer
412 views

Can I use conditional formatting to highlight a cell for dates less than 30 days away?

I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
1
vote
3answers
7k views

Formulas in Google Sheets won't execute

I can't seem to get formulas to execute in a Google spreadsheet. Whenever I input a formula, it does not return any output, but rather leaves the cell blank. I have tried the following troubleshooting:...
2
votes
1answer
416 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
0
votes
1answer
8 views

How to paste a comma separated string into one column in Google Sheets?

For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
1answer
25 views

How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
1
vote
1answer
208 views

Change text case so that first letter of the sentence is capitalized

Want to automatically change text case as I type on Google Sheets so that first letter of the sentence is capitalized.
1
vote
1answer
340 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...