## Hot answers tagged google-spreadsheets

23

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, ...

7

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 ...

7

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 ...

6

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 ...

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)))

5

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):
...

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

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

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

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

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.

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

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

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 ...

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

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 ...

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

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 $).

2

You could select the complete column C by selecting the header and paste the formula =A1*(1.6*B1) .
it will apply to every row.
No need to select and drag to copy to every cell.

2

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

2

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:
...

2

You can open the document twice and have the first tab set on the first sheet and the second tab set on the second sheet. All you have to do is go back to the Google Docs file browser and click to open the file again.
I have tested that:
If you edit the second sheet in the
second tab, then open the second
sheet in the first tab, it will have
all your ...

2

NoCatharsis,
The simple answer I have for you is Excel specific (Google Docs didn't have similar filters and Open Office's Calc copies filtered cells):
1) Use "AutoFilter" (Data>Filter>AutoFilter) and filter for non-blanks.
2) Copy remaining cells
3) Paste Special (Edit>Paste Special) and select "Transpose" from the options
JDB

2

You should be able to do that with conditional formatting in excel, I'm not sure about the others. The exact method will depend on the version of excel.
EDIT: This should be what you need I don't have 2007 to test it though
In Excel 2007:
Select range
On the Ribbon, go to the Home tab and click Conditional Formatting
Click New Rule
Click Use a Formula ...

2

In OOo as well as in Excel, just combine an absolute reference for the column with a relative reference for the row. This way, you can copy the conditional format to every cell, and it will evaluate the values of cells A and C in the current row.
In OOo, you will have to define the conditional formatting as follows:
Select a cell in row 1 that should be ...

2

To create your multiset in Column D, enter the following in D2:
=B2
Enter the following in D3 and fill down until you get a '#REF!' error:
=IF(COUNTIF(D$2:D2,D2)=INDEX($A$2:$A$7,MATCH(D2,$B$2:$B$7,0)),INDEX($B$2:$B$7,MATCH(D2,$B$2:$B$7,0)+1),D2)

2

In Excel VLOOKUP "Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array."
Taking your info as posted, I created a table on the first sheet and this on the second sheet;
=VLOOKUP(A2,Sheet1!A1:D6,2,FALSE)
Type the name into A2 and B2 will look up the color value for the name ...

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