Skip to main content

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
5 views

How to return values horizontally in Google Sheets [migrated]

Say I have a column like this: User1 Item1 User1 Item2 User1 Item3 User1 Item4 User1 Item5 User2 Item1 User3 Item1 How can I return them like this in a filter/lookup formula: User1 ...
yesnomaybe's user avatar
0 votes
1 answer
41 views

How can I keep multiple rows of data while only displaying one row, but the others show up when moused over or selected?

Basically, we have a rickhouse full of barrels. Each rick/slot is 3 levels/rows high. We have 180 ricks/slots, or 540 levels/rows. Each row is 10 barrels deep. So each barrel has a rick number, a ...
Harry Jarvis's user avatar
0 votes
2 answers
86 views

How do i remove numbers before text in excel

I want to remove numbers before questions in the column C the column name is ***QUESTIONS***
Maddy plus plus's user avatar
0 votes
1 answer
26 views

Leave cell blank until data is entered when multiplying

I have very little experience with spreadsheets and computers in general compared to, I assume, all of you. So, if you could spell out the answer to the below question as clearly as possible, it ...
Brettzkynh's user avatar
0 votes
0 answers
5 views

Growth between 3 cells in Google Sheets [migrated]

I am creating a data wall to compare student achievement across 3 time periods Beginning of the Year(H2), Middle of the Year (I2) and End of the Year(J2) and having the difference recorded in K2. I ...
Justin Barry's user avatar
4 votes
1 answer
143 views

Consolidate Data from Multiple Columns into One Column

I have a dataset that looks something like this: Customer Col1 Col2 Col3 Bob 111 2221 Michael 112 Anna 113 2223 Rachel 114 2224 334 Person5 115 I'd like to put all data into one column, ...
Beef_tataki12's user avatar
0 votes
2 answers
29 views

formula to count in how many cells first digit is greater than second in last pair

Let's say I have row of cells A1:A10 with values like "46 64 76", "63 60" etc. Two or three pairs of digits separated by spaces. I need to count in how many cells in last pair ...
Dork's user avatar
  • 101
1 vote
1 answer
50 views

SUM of a range of durations in an XLSX-Formatted sheet always returns 0 or errors

Excel document I have the following XLSX-formatted spreadsheet in Google Sheets I'm trying to set a formula on cell R5 (colored in red) so that it adds up all the times in the range L4:N42 but ...
MoRryN's user avatar
  • 13
0 votes
2 answers
84 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
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
86 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
106 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
68 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,708
-1 votes
1 answer
52 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
1 answer
173 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
18 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
115 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
94 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
46 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
20 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
58 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
45 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
125 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
112 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
2k 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
49 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
519 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
355 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
203 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
  • 3,156
0 votes
1 answer
52 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
647 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
44 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
  • 984
-1 votes
1 answer
38 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
690 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
235 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
2k 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
460 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
172 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,867
0 votes
1 answer
2k 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
1 answer
101 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
227 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
963 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,030
0 votes
1 answer
470 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
121 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
9k 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
459 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
2 3 4 5
10