One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
2answers
659 views

Categorize cells in Google Spreadsheets

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this: | Who | What | Category | When | Amount | | Gui13 | Vegetables | Meals | ...
1
vote
1answer
2k views

How to split, trim, and sum up numbers in spreadsheet text cells?

I have a series of cells in a Google Spreadsheet containing string such as: A1 = "X5.1 Y4.3 Z2.8" B1 = "X2.5 Y1.9 Z4.8" C1 = "X6.2 Y1.9 Z3.4" I need to get the sum of all the numbers that appear ...
1
vote
2answers
1k views

Is there a limit on functions in Google Spreadsheets?

As to answer a question on SuperUser, I was making a sample spreadsheet on google docs. Now I made it first in Excel 2010 on my computer to copy/paste it later on. When I tried to copy a formula that ...
1
vote
1answer
3k views

Google Spreadsheet formula, MINUS?

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
1
vote
2answers
32 views

Batch Google search or search inside Google sheet

I have a list of cities that I want coordinates for. I've been typing in the city in Google Search (in the format: city name coordinates—for example Recife coordinates) and then pasting the result ...
1
vote
2answers
63 views

How do I change a string which encodes a date into a date?

I have some cells in the format 15/05/15. Here is an example. These are dates, but as I copied them into Google Spreadsheets, they are apparently treated as strings. How do I convert them into date ...
1
vote
1answer
212 views

Calculate compound interest rate in Google Spreadsheet

Assume the PV=100, FV=150, yearly IR=X, number of period = 60 (5 years of interest and compounding is monthly basis) Therefore: FV = PV * (1 + X / 12)^60 How can I calculate X easily? ...
1
vote
1answer
397 views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
1
vote
2answers
392 views

How can I make Google Sheets tell me how many of each date are in a column?

I have a column in my spreadsheet that contains only Date values, like: 2014-05-17 2014-06-19 2014-07-16 2014-07-16 2014-07-21 2014-07-21 2014-07-21 2014-07-21 2014-07-21 2014-06-17 2014-06-24 ...
1
vote
1answer
50 views

How do I move a Google Spreadsheet document from one Google account to another?

I created a moderately complex Google Spreadsheet document in my personal account, which become popular amongst my colleagues and is now officially adopted by our team. Therefore, it would make sense ...
1
vote
3answers
162 views

Publish single Google Sheet to the web

I have many sheets in one spreadsheet. How do I publish single spreadsheet to the web? When I attempt to publish, it doesn't show any option to pick the sheets I want to publish.
1
vote
1answer
85 views

Pulling most-frequently used names from across Google Spreadsheets and ranking them on a master sheet

I have created a spreadsheet with a "master" sheet and several individual sheets. On each individual sheets, I hope to have each one of my colleagues input names. The master sheet should then be able ...
1
vote
2answers
3k views

Using combined IF THEN with IMPORTRANGE formula in Google Spreadsheets

I am looking to understand what the correct syntax would be for a formula for a Google Spreadsheet that would use the IF THEN function inside of an IMPORTRANGE formula. See the two workbooks I ...
1
vote
3answers
3k views

Return a blank when cell reference is blank—Google Spreadsheet

I am using the following formula in a Google spreadsheet to calculate the number of days between two dates (dates are in columns G and AL). The problem is that some of the cells in col AL are blank, ...
1
vote
1answer
4k views

How can I import Google Calendar events into a Google Spreadsheet?

I just need rows with name, date/time, and location. Ideally it should be done automatically every n hours or at every event added. I found a script solution on Stack Overflow, but is there any ...
1
vote
1answer
2k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
1
vote
1answer
747 views

Selecting a sequence of sheets in Google Docs / Spreadsheets

I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically. I would like to summarize some information from all sheets into ...
1
vote
1answer
458 views

Combining Stuff in Google Spreadsheet

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use: CONCAT: =CONCAT("TEST",B2) & ...
1
vote
2answers
127 views

Javascript string() function in Google AdWords

Is there a simple way to implement the Javascript string() function in the basic Google AdWords interface? I want to pass some variables returned from a campaign management script to a spreadsheet ...
1
vote
2answers
2k views

Return an Offset Cell Value, Where Last Occurrence in Range is Matched

I have a worksheet like this: || A | B | C | ---++--------+-------+-----| ---++--------+-------+-----| 1 || 1 | a | | 2 || 2 | b | | 3 || 3 | X ...
1
vote
1answer
1k views

What's the biggest csv file you can import into a Google Docs Spreadsheet?

I'm trying to upload/import a 7mb csv file to Google Docs without much luck; What's the largest file size that you can import?
1
vote
1answer
31 views

Calculated field in Google Sheets not working

I have a pivot table created in a Google Sheet that looks like this: Desktop | Mobile Sessions | Trans. | Sessions | Trans. | 250 | 34 | 150 | 25 | .... | ... | ...
1
vote
1answer
17 views

Get unique rows and their count in Spreadsheet

I have a sheet as below, | A | B | C | ------------- |abc|123|Yes| |jkl|123|No | |abc|123|Yes| |abc|567|Yes| I want to get the output like, | A | B | C |Count| ------------------- |abc|123|Yes| 2 ...
1
vote
1answer
12 views

How do I change the year in Google spreadsheets

I'm copying and pasting things over from an old spreadsheet and the original dates where marked like '25/4' so dd/mm but I never included the year, well the problem is these where dated in 2014 but ...
1
vote
1answer
44 views

If time is before 17.00 use 17.00 OR if time is after 17.00 use value in cell

Headline says it all. I'm trying to calculate worktime in minutes. Ex: https://docs.google.com/spreadsheets/d/1GmGyi4kQf8I2qvEJjfG3OZvm_n_kBbsw_doTi9J0KCk/edit?usp=sharing I have to state when ...
1
vote
2answers
26 views

Show number in a cell - based on a value from another cell

I need help with the question on the picture. Can anyone make a formula for me? Thanks
1
vote
1answer
109 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
129 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
1
vote
1answer
724 views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
1
vote
3answers
2k views

How can I replace blank cells with zeros in Google Spreadsheet?

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s? I tried find and replace with regular ...
1
vote
2answers
3k views

How do I apply formatting to a cell based on comparison with another cell?

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns: A (Breed) | B (Male) | C (Male Goal) | D (Female) | E ...
1
vote
2answers
151 views

Calculate time values in a single Google Spreadsheet cell

Is there a way to add two times, or durations, in one line in a Google Spreadsheet? Something like: =1:14:25+0:17:12 I know that I can use the time command in-line, and that I can add times from ...
1
vote
1answer
255 views

Menu option Insert -> Script… is missing in my Spreadsheet, how do I fix it? [duplicate]

I've 2 documents, in 1st one I was able to add 'Script...' (as suggested here) from the Insert menu, but on the 2nd one and all new which I'm creating I don't have this option present. Why this ...
1
vote
1answer
208 views

How do I import text file as-is to Google spreadsheet?

I'm importing a text file with some columns being 000000, 0000, 000000000. Google Docs converts them all to 0 - treating them as numbers. How do I make the columns contains exactly the string that was ...
1
vote
3answers
997 views

How do you automatically align incoming form responses in Google Sheets?

I like to have each of my form responses aligned in the center of their cells. However when I submit any new information through Google Forms, they are always automatically aligned to the left, and so ...
1
vote
1answer
51 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
1
vote
2answers
577 views

How can I easily fill a column in Google Spreadsheet with random values from a list?

I need to create some data for a simulation. So I wanted to take a spreadsheet, and for each column, fill it with random data falling within a domain. A uniform distribution is good enough. For a ...
1
vote
1answer
99 views

Using SUM() and VALUE()

I have a spreadsheet with invoice amounts. Each amount is a link to the given invoice. Can I use sum() and value() (or any other functions) to somehow use the displayed value for sum() (and not the ...
1
vote
2answers
231 views

Google Spreadsheets: Add 1 if there is data in cell

I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected. I want ...
1
vote
1answer
295 views

Sum every 9th row starting from the 4th row in Google spreadsheet

I have a column where there is a number every 9th row. The problem is that my first value starts on 4th row then all the next ones every 9th row. So far I tried this: ...
1
vote
2answers
47 views

Is there a more efficient formula to use than just copying and pasting?

D3 =(B3^3.0)-(B2^3.0) D4 =(B4^3.0)-(B3^3.0) D5 =(B5^3.0)-(B4^3.0) D6 =(B6^3.0)-(B5^3.0) D7 =(B7^3.0)-(B6^3.0) .... I rarely used Excel but since I don't have it installed on my computer I'm using ...
1
vote
1answer
1k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
1
vote
2answers
96 views

What is the best approach to adding additional costs to items in Google Spreadsheets?

In Google Drive I'm keeping a spreadsheet of everything I spend money on. I have a heading for the cost but alone that's insufficient. For example, if I tip after a meal I really want to have that ...
1
vote
3answers
2k views

IF statement in Google spreadsheet

I am trying to sum up the total of all values in a coloumn that are POSITIVE. Also, I have some subtotals in the coloumn that should be left out from this sum. =SUMIF(E6:E65 ; ">0" ) - ...
1
vote
2answers
528 views

display google docs spreadsheet in mobile mode in desktop browser

When viewing a google docs spreadsheet in a mobile browser you have handy filter buttons at the top. How do I display mobile version of the page in a desktop browser? Preferrably Firefox. Edit: I seek ...
1
vote
1answer
2k views

How can I alphabetize a unique result in Google Spreadsheets?

I'm generating a list based on using the UNIQUE function. How can I get the output to be alphabetized automatically?
1
vote
1answer
6k views

Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
1
vote
3answers
1k views

Missing sort in Google Spreadsheets menus

I have a Google spreadsheet that has the A column as an ID field (incrementing numbers 1...1000; no formula, just numbers). That column is frozen as is the first row of headers. I sorted by column B ...
1
vote
1answer
45 views

Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns

Trying to determine a way that I can use a custom menu (.createMenu) with views that will allow me to show / hide specific columns. I need the capability to pick specific multiple columns and not ...
1
vote
1answer
17 views

How to select all cells between two cells, where more may be inserted later?

I am trying to count all the cells that have information between two cells that are in the same column. This is easy enough to do using =countif(cell1:cell2, "<>") the problem is that I may ...