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.

Filter by
Sorted by
Tagged with
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 ...
dee123's user avatar
  • 1
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....
Dennis 's user avatar
  • 113
-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 ...
Anakin Teow's user avatar
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&...
NealWalters's user avatar
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 ...
RobKohr's user avatar
  • 127
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 ...
Ramya's user avatar
  • 35
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 ...
Lucasheythere's user avatar
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 ...
user1889141's user avatar
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 ...
dennisklad's user avatar
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 ...
Nikki's user avatar
  • 9
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 ...
attavip's user avatar
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 ...
soapergem's user avatar
  • 1,648
-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 ...
Matt's user avatar
  • 109
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 ...
Jesse Rutschman's user avatar
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 ...
Sebastian's user avatar
  • 121
-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 ...
Maciek's user avatar
  • 121
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$...
Abdullah Al Mahmud's user avatar
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 ...
Saeed Neamati's user avatar
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 ...
John Smith's user avatar
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 ...
rxmnnxfpvg's user avatar
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 ....
sevgi's user avatar
  • 1
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 ...
TheSparcy98's user avatar
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 ...
Abdulrahman Mohamed's user avatar
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,...
depperm's user avatar
  • 113
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?|
Vu Le's user avatar
  • 3
-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: ...
changjx's user avatar
  • 101
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 ...
Santiago Garcia Cuevas's user avatar
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 ...
Agard's user avatar
  • 1
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 ...
Blindspots's user avatar
  • 2,842
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 ...
MichaelB's user avatar
  • 101
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 ...
dcorks's user avatar
  • 23
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 ...
questioner56's user avatar
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....
ahsteele's user avatar
  • 1,914
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 ...
LWC's user avatar
  • 926
-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 ...
Evan Srinasa's user avatar
-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 ...
helloandyhihi's user avatar
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:...
fred's user avatar
  • 113
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 ...
Sarah Savoy's user avatar
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 --> ...
user3871's user avatar
  • 201
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 ...
AlainD's user avatar
  • 4,675
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 (...
user1779906's user avatar
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: ...
Diego R's user avatar
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 ...
Faustin Gashakamba's user avatar
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:
Nikos's user avatar
  • 630
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 ...
Phrogz's user avatar
  • 1,020
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 ...
MDuh's user avatar
  • 203
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 ...
CodePanda's user avatar
  • 153
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 ...
Moshe's user avatar
  • 225
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 ...
hyrshe's user avatar
  • 13
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 ...
MilindaD's user avatar
  • 113

1
2 3 4 5
10