Questions tagged [google-spreadsheets]
Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.
477
questions
0
votes
0
answers
8
views
Google Sheets - Multiply/Concatenate String Arrays [migrated]
Suppose I have three columns importing data from elsewhere, like so:
Name
Company
Division
Adam
Meta
Ops
Betty
Nvidia
Sales
Carl
OpenAI
Development
The number of values in each column may change ...
0
votes
0
answers
7
views
Google Sheets no menu iPad [migrated]
My GoogleSheets app on a late-model iPad Pro looks like . I would like to select all rows and columns of this data (700+ columns wide) and create a line graph that shows these sunrise times over time....
-2
votes
0
answers
10
views
How to sum across columns automatically? [migrated]
Would like your help to solve the problem as file attached.
https://docs.google.com/spreadsheets/d/1E3KTOYHuYIJy9uQKsvlXwbCpwL6cU1IHAQ-Yirb-AfQ/edit#gid=0
I would like each column to sum automatically ...
0
votes
0
answers
10
views
Google Sheets - Recorded a Macro to Import a File [migrated]
I'm able to import a file from my disk using "File Import".
I tried to do the process while recording a macro.
I did "File", "Import", then chose "Upload new file&...
0
votes
2
answers
28
views
Get first and last values for a range
Say I have a range, A1:A27, and that range has a mix of empty and non empty values, I would like two functions: one to get the first non-empty value in the range, and one to get the Last non-empty ...
0
votes
0
answers
5
views
Counta and Array_constrain formula in Google Spreadsheets [migrated]
I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...
0
votes
0
answers
11
views
Google Form validation from a Google Sheet for Inventory [migrated]
I'm trying to make an inventory system and am struggling with using a google form to check out inventory. I would like the form to validate that the products being removed from inventory are actually ...
0
votes
0
answers
29
views
How to create a timeline diagram from multiple data values in sheets?
My data represents tasks and subtasks that I want to create a diagram from:
But as you can see in chart below, it treats multiple values as one value:
I tried separating the values into different ...
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
0
votes
1
answer
55
views
How to get value using row and column search
I made a budget spreadsheet I'd like to use and have plugged in some numbers to ensure I'm doing everything correctly. I have two sheets: 1. for the budget i.e. I'd like to spend max $1000 for rent ...
0
votes
0
answers
59
views
Sync Google Sheet with Microsoft Form Responses
I have a Microsoft form and the responses are recorded in an Excel file. I want to sync this Excel file with a Google sheet to do some calculations there. Sometimes, some formulas break the connection ...
0
votes
1
answer
55
views
How can I get an aggregated count of rows grouped by two values and enforcing uniqueness on a third in Excel?
I have a Google Sheets spreadsheet which is connected to an external integration that automatically populates rows with new 'events' contained within one of the sheets. I want to create a second sheet ...
-1
votes
1
answer
38
views
Alpha-sorting multiple rows, empty cells for missing values
Say I have multiple columns:
A | B | C…
Bill | Bill | John
Adam | Fran | Nick
| Adam2| Adam
I’d like to alpha sort the rows in each column, and also add empty values in a cell if the cell ...
0
votes
0
answers
79
views
Using Array Formula with TEXTJOIN to separate with commas all non-blank fields in each row
I am trying to create an array formula function that will pull the values of all fields in each row (between columns F and V) and separate them by ',' when not blank (from within only the row). The ...
1
vote
0
answers
15
views
What's the best way to draft emails in Gmail that use variables from Sheets? [closed]
I'm want to draft emails in Gmail that incorporate variables from Sheets. Is there a free tool that does this, or am I better off writing a script?
Crucially, I need to look over the draft emails ...
-1
votes
2
answers
82
views
Difficult Cumulative Income Tax Calculation
I've been trying to solve this for days. I can't seem to figure out how to create a one size fits all formula for this cumulative tax.
I'm trying to achieve the following:
I can't seem to find a one ...
0
votes
2
answers
85
views
How to shorten Excel OR function for many conditions?
As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF.
My formula: =if(or(b3/b$2<0.4,c3/c$...
1
vote
1
answer
43
views
How to use countif to find specific formulas
I'm using Google Sheet. I have a bunch of =char(10003) to show check marks in some cells.
I want to use =countif to count them all.
I'm stuck at what to write for the criterion parameter.
How can I ...
0
votes
1
answer
17
views
Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text
A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...
0
votes
1
answer
35
views
SUMIF or VLOOKUP for comma-joined / array
See toy example (Google Spreadsheets). Given column D, what formula do I need to enter in column E so that it gets the sum of the comma-separated list of values present in A? You can assume all items ...
0
votes
1
answer
35
views
GDrive calculates matrix multiplications differently in GSheets and . xlsx. Does anyone know why this is?
I've been using Google Forms and Google Sheets to gather and analyze data for some time. I use array formulas and calculate matrix multiplications. For back-up reasons, I had to export the Sheet as ....
0
votes
1
answer
34
views
How can I copy value only if another column says "Completed"?
How can I copy value to another tab from column "price" in which I type price only if column "status" says "Completed"?
I have Appointments tab in which I type prices of ...
1
vote
1
answer
95
views
replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet
I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...
0
votes
3
answers
109
views
Return First Column Header of Instance of String
I have a collection of speakers who have spoken various dates.
Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018
1st-Speaker,Jim,John,Fred,Sam,Scott,Riley
2nd-Speaker,...
0
votes
1
answer
1k
views
MAX function not working on date with format dd/mm/yyyy in Google Sheets (Excel)
I'm using Google sheets and the MAX() function works only on dates with format mm/dd/yyyy. How to get the MAX() of date with format dd/mm/yyyy?|
-1
votes
1
answer
44
views
how to split one string into multiple rows (by every eight chracter)? [duplicate]
i have some data below, could there be anyway to split this all string data into multiple rows, each row with 8 characters. Appreciates ur help. ^^"
Raw data:
...
0
votes
1
answer
279
views
Google Sheets - Making a chart where certain series do not have data for all X-axis values
I am making a chart for research in musicology which requires me to graph the duration and tempo of notes based on the score, i.e. musical values like a quarter note.
Using a recording of a string ...
0
votes
0
answers
295
views
Calculus operations in Excel and Sheets
I am having issues with integration in Google Sheets and Microsoft Excel. I have installed the necessary packages to compute integrals and double integrals, including Calculus Functions for Google ...
3
votes
1
answer
183
views
Text transformation of regex capture group using PROPER is ignored by REGEXREPLACE
I am trying to change the case of part of a text string (sub-string) in Google Sheets.
I've tried and failed to apply the PROPER function to a regex capture group inside REGEXREPLACE.
The PROPER ...
0
votes
1
answer
50
views
Find stdev of values in col B where col A matches criteria
I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days.
I am able to do sums with a sumifs which works on ...
2
votes
1
answer
492
views
How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)
I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...
0
votes
0
answers
45
views
How to verify calculations when Excel and Google Sheet give imprecise results [duplicate]
This question is similar to In Excel, when I enter 22222.09482 then I see 22222.0948199999 number in the formula bar but it isn't the same. It's about how to verify with precision.
A simple ...
0
votes
2
answers
43
views
How to determine cell value based on comparing other cells to find the fastest and slowest durations?
I have a set of columns in a Google Sheet which are calculating the average duration between two times. I would like to calculate a score in the cells marked with an X based on the calculated averages....
0
votes
1
answer
14
views
How to dynamically return in each row the number of occurences of a string found in every column?
I have data between H2:L where H is static but L can grow to a new column.
How to use something dynamic like an arrayformula to count the number of occurrences of certain strings inside every column ...
-1
votes
1
answer
35
views
Convert tally table to a table
I have a table in Google sheets which approximately looks like this:
Name
Red
Yellow
Green
Blue
Mark
1
John
1
Steven
1
Bill
1
I want to shorten it into this kind of table:
Name
Color
Mark
...
-2
votes
1
answer
491
views
Excel / Sheets: How do I filter addresses based on a list of zip codes?
I'm planning to mail some postcards to potential business contacts.
I've got a spreadsheet of 2,500+ addresses with columns including Name, Street Address, City, State, zip code, etc. that cover the ...
1
vote
1
answer
156
views
reference range in different sheet with Indirect column and row
I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this:
=INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE)
instead of
=dataset!a1:...
0
votes
1
answer
1k
views
Len and substitute to return 0 if cell is blank?
I'm using BYROW to pull a string of dates from one sheet and make a list of those dates in a single cell on another sheet. Now I'm trying to use LEN and SUBSTITUTE to count the number of dates in that ...
2
votes
1
answer
344
views
Remove third-party Google Sheet Add-On and UI Menu
I installed Numerous.ai third-party Add-On for Google Sheets (CAREFUL - DO NOT INSTALL IT). I didn't like it, so I then deleted it from Add-Ons (Extensions --> Add-Ons --> Manage Add-Ons --> ...
0
votes
1
answer
128
views
Replace duplicates in-place (Excel or Google Sheets)
Is there in-built functionality to remove duplicates in-place in Excel (365) or Google Sheets across several columns? For example, consider this set of data:
The goal is to select the entire data ...
0
votes
1
answer
759
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
0
votes
0
answers
66
views
Combining sheets with different number of colums and rows
How can I combine two sheets with different number of rows and columns in Excel, Libre Office or Google Sheets? Example:
Sheet 1:
Name
Age
Phone
Mike
35
197 158 348
Dave
38
394 953 429
Sheet 2:
...
1
vote
1
answer
1k
views
Drag and a drop a range from one sheet to another sheet of the same workbook or spreadsheet in Excel/Google Sheet
I would like to move a range of cells that contains many formulas (some using absolute reference and some others using relative reference). If I just do Cut and Paste then the relative formulas will ...
1
vote
1
answer
196
views
Calculate the difference in days between dates between rows in Google Spreadsheets?
I have a date column. What formula do I use in Google Sheets to calculate the difference in days between rows?
I tried =DATEDIF(A2,A3,"D") but get the following:
0
votes
1
answer
717
views
Performing T-test on subset of values in a Google Sheets range
I have a set of sports scores in Google Sheets. I have various subsets of scores for which I calculate the averages and standard deviations. I want to perform T-tests to determine if a particular ...
0
votes
1
answer
372
views
Google sheets is able to access protected sheets and workbook. How to properly protect them?
I was making a quiz template on Excel with all the correct answers on a separate sheet so it can be compared and then graded, again in another sheet on the same file/workbook. The two other sheets are ...
0
votes
1
answer
106
views
Bulk edit a column and add formulas
I have a column which contains values like
2015
2000
1950
I want to replace this column with something like
=YEAR(TODAY())-2015
=YEAR(TODAY())-2000
=YEAR(TODAY())-1950
How do I do it, if I have ...
8
votes
4
answers
7k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
1
vote
2
answers
438
views
Using XLOOKUP or FILTER to aggragate data into a single cell
I have a data set that is compiled by various people filling out a form to populate a spreadsheet on Google Sheet. Here's what the data set might look like:
A data set listing out name, collected ...
1
vote
1
answer
58
views
How to group by multiple excel columns into one list with a header
So I have a dataset in Google Sheets (Excel) like the following in two columns
Movie1, Jack
Movie1, John
Movie1, Henry
Movie1, Hanson
Movie2, Bill
Movie2, Benny
Movie3, Gary
Movie4, Robert
I want to ...