## Hot answers tagged google-spreadsheets

59

Using the Mouse
Click the cell whose formula you want to repeat
A dark square "handle" will appear in the lower right corner
Click and drag that handle, dragging down the column (or right across the row)
Stop at the last cell you wish to fill
Using the Keyboard
Move the cursor to the cell whose formula you want to repeat
Hold shift
While holding, ...

37

An even easier solution in Google Sheets would be the formula, entered in C1:
=ARRAYFORMULA(IF(A5:A,A5:A*(1.6*B5:B),""))
It will automatically replicate ("Continue") to succeeding rows if a value is entered in column A.
No need to copy it to each row. In fact, if you copied it to C2 it would be automatically overwritten by the continuation of the formula ...

12

here is a another way, go ahead and delete all the formulas that are in there right now, then type in the formula in C1 having it correspond to A1 and B1 and hit enter.
so now the correct formula is just in C1,
now click the C1 box, a bounding box will appear,
the bottom right corner of this bounding box has a dark square,
double click this square and the ...

11

As far as I know there are no built-in features that can parse and summarize comma-separated tags in Excel. You can, of course, create your own solution with worksheet functions and a little VBA. Here's a quick solution for doing this.
Step 1: Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom ...

9

Recommended way is to use TEXT() function.
Quick summary on how you would use it in your case:
=TEXT(5,"000")
Would output:
005
Of course you would substitute the literal 5 with a reference to another cell where the source number is.

8

Google spreadsheets has a series of functions for external data. If you can find (or create) a site which does the lookup by passing parameters, you could put a formula similar to this in:
Cell A1 (Address): 123 Main St
Cell B1 (City): Springfield
Cell C1 (State): MO
Cell D1 (combined address): =concatenate(A1,B1,C1)
Cell E1 (imported zip code):
=...

6

The code
=SUMIF(D$2:D$30,"<="&A5,E$2:E$30)
should work in both Excel and Google Spreadsheets.

6

I dont use spreadsheets other than Excel, but Excel is capable of doing just about anything using VBA, including custom functions. Check out this article to see a basic example.

5

For a one cell solution you can use an "array formula" like this in excel
=SUM(IF(A2:A6>B2:B6,A2:A6,B2:B6))
confirmed with CTRL+SHIFT+ENTER
In google docs you should be able to do the same - either use CTRL+SHIFT+ENTER with the above or use
=ARRAYFORMULA(SUM(IF(A2:A6>B2:B6,A2:A6,B2:B6)))

4

You can't as of yet. According to Google's support page, Rows and columns: Freezing columns and rows:
Freezing Columns
There are also two methods to freeze
columns in a spreadsheet:
The Column Bar is a vertical bar that separates active columns from
frozen ones. Using its handle (found
at top, by the column headers), you
can drag the ...

4

Now import the table query again, it will replace the dummy table you created before.

4

There won't be anything built into a spreadsheet as verified address databases are very expensive.
You might find an online service that will do this for you. For example in the UK the Royal Mail website allows you to look up postcodes (the UK equivalent to zip codes), but you are limited to 15 searches a day. This is for personal use. Businesses will get ...

4

The freeze menu item now lists the first two rows/columns or all the way up to the currently selected cell:
This can also be done by dragging the frozen column handle at the top of the column:

4

There is a huge amount of real world data freely available on various EU websites, such as Eurostat. All can be exported as .xls or .xml.
Most national governments will have some similar statistics portal, although some require login/subscription for the raw data.

4

Create the form using New > Form menu. When you create a form in Google Docs, it automatically stores the responses in a spreadsheet. In the spreadsheet, you'll find related items in the Form menu item. Refer to the Google Help instructions for working with forms.

4

There's a function on Google Spreadsheets called ARRAYFORMULA. Here's how to use it in your case:
=ARRAYFORMULA(SUM(IF(EXACT($B$2:$F$2,B3:F3),1,0)))
This saves from the repetition of typing out:
=SUM(IF(EXACT($B$2,B3),1,0), IF(EXACT($C$2,C3),1,0), IF(EXACT($D$2,D3),1,0), etc...)
Assuming your spreadsheet was setup like this:
A B C D E ...

4

Apple iWork Numbers
This cannot be done in Numbers, unfortunately.
Google Apps
Custom functions are supported in Google's spreadsheet, using JavaScript.
LibreOffice Calc
LibreOffice Calc supports custom functions.
Microsoft Excel
Custom functions are possible in Excel, going back at least as far as Excel 2003.
Given that there are several choices, ...

4

Create a blank Column (D) and use "CONCATENATE":
=CONCATENATE("0049",C1)
Drag that down for each adjacent cell in Column C and it will add 0049 as a prefix.

4

You're nearly right. You can adapt your formula slightly like this:
{=SUM(IF(A1:A5=B1:B5,1,0))}
Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.

4

For Google Spreadsheets
Google Spreadsheets has these cool already-builtin regex formulas
REGEXEXTRACT, REGEXMATCH, REGEXREPLACE
We use the first one to extract mail adresses. Put this formula in a Google Spreadsheet cell:
=iferror(Regexextract(A1;"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}");"")
A1 is the cell where the string to check (mail body in ...

3

Yes - You can now find the Zip Codes in your Google Docs sheet using the following URL:
Step 1: In this landing page, enter the address which you would like to search.
Step 2: I searched for the "1245 5TH ST" address in Washington, DC and the URL looked like this:
https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=...

3

It's hard to do in Excel. You would need to call a Web Service using SOAP toolkit, or use Visual Studio. One must wonder what Microsoft is thinking.
It's much easier to do in a Google Docs Spreadsheet, then export to Excel:
You can use GeoCoder.ca's XML web service to look up the zip code by address. In Google Docs, you would use this function:
=importXML(...

3

You do need some sort of external address lookup tool or service. With just City/State, there is often no way to determine the correct zip code as many cities have multiple zip codes and many cities have streets which span multiple zip codes so you can't even add just the street and use a city/state/zip formula or lookup. If you need to find zip codes for ...

3

This is the simplified version of the formula you provided in the question:
=IF(ISNA(VLOOKUP(C24,'Ability Base'!A$2:B$50,2,FALSE)),
"Not Ability",
VLOOKUP(C24,'Ability Base'!A$2:B$50,2,FALSE)
)
Maybe you can tell your requirement in words if the above is not suitable.
(Tested in Excel 2003)

3

Wow, after searching around for a month on this problem, I stumbled upon the answer after posting this question. Here is what I came up with in case anyone else has this problem.
=SUM(IF(ISNUMBER(FIND("John"; SPLIT(A1; ",")));1;0))
This is an array formula so will need to be entered using Ctrl+Shift+Enter.
If anyone thinks of a better way to solve this ...

3

Why not you just use the sample files located in under C:\Program Files\Microsoft Office\Office11\Samples directory?
They are sample data from a fictional company called "Northwind", and are designed to simulate real company data that can be used to teach Excel tricks.

3

P.S. I am working in OpenOffice, and now I see that it also works by simply copying the content of the cell and pasting it into the other ones. The formula is automatically adjusted to each row! (To avoid the automatic adjustment prefix the name of row number and column numbers with a $).

3

The *gotchas" occur when a file is imported or exported. This is not something you can avoid by saving to a more application-independent format (such as .ods) because Excel still has to translate into that format — and MS Office export filters do not have a good reputation. There are third party ODF plugins for MS Office that are said to do a better ...

3

In Excel you can use SUMPRODUCT function in a similar way to Andi's solution, but avoiding "array entry":
=SUMPRODUCT(0+(A1:A5=B1:B5))
that will also count any blank rows (as does Andi's) so if you want to avoid that change to this version:
=SUMPRODUCT((A1:A5<>"")*(A1:A5=B1:B5))

Only top voted, non community-wiki answers of a minimum length are eligible