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

learn more… | top users | synonyms (2)

1
vote
1answer
1k 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
1answer
17 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? Technically I can ...
1
vote
1answer
46 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
146 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
82 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
2k 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
2k 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
3k 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
668 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
390 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
124 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
1k 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
33 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
21 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
1k views

How to import JSON data into Google Spreadsheets?

There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there any similar solution to import data in JSON ...
1
vote
1answer
96 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
91 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
363 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
2answers
1k 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
122 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
139 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
705 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
48 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
469 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
868 views

How do I combine a cell value with a string in a Google Spreadsheets formula?

I have the following IF condition in some cell: =IF(A1>A2, "value is <C5>", "value is <D5>") where <C5> and <D5> should be the values of cells C5 and D5. I know it is a ...
1
vote
1answer
88 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
192 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
2answers
43 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
2answers
90 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
1k 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
502 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

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

Put Google search inside Google Docs spreadsheet cell

Is there a way to put a Google search widget inside a cell of a Google Docs spreadsheet? I want to avoid my users to hit Ctrl + F every time they search. Would be very cool a Google Docs App that ...
1
vote
1answer
13 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
1
vote
1answer
20 views

Script to Create Copy (Backup) of entire spreadsheet onOpen

Is it possible to write a script or is there another method of backing up a spreadsheet when opened? The reason is: I have other users opening my spreadsheets and if they completely stuff everything ...
1
vote
1answer
51 views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google docs and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select ...
1
vote
1answer
24 views

How can I get a unique ID when I submitted an answer in Google Form?

I am using Google Form for my customers to submit their opinion. I read this and this, but I have some questions which these two posts haven't mentioned. Is it possible for my customers to know their ...
1
vote
2answers
62 views

How to insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets? Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say ...
1
vote
2answers
59 views

Google Query For Dates in Last 30 Days

I'm trying to figure out how to do a google query for dates in the last 30 days. I'm using a wordpress plugin that lets you display spreadsheet data and do queries. Here is the current working query ...
1
vote
1answer
69 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
1answer
24 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
33 views

How to count specific items in column?

I just started using Google Spreadsheets. I have a question concerning "counting" a specific word/item in cells. Example: Column A 1. HAT 2. HAT 3. SHOE 4. CANE 5. SHOE 6. HAT 7. - 8. - ...
1
vote
1answer
56 views

Delete second row in a cell in Google Spreadsheet

In order to create some list, I've been copy some data from the web. Unfortunately it looks like it has been added second row for each record it need. For example: Each record is in the same ...
1
vote
2answers
40 views

Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...