## Hot answers tagged google-spreadsheets

31

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

15

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

10

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

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.

6

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

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

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

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

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

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

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

This can now be done by dragging the frozen column handle at the top of the column:
Even though the pull down options still only list 5 columns avaialable:

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

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

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

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

2

If SUMIF isn't given a sum_range, it simply sums the values it finds that meet the criteria. The simplest way to add the number of 1's is to ask SUMIF to check for them in cell B14:
=SUMIF($E$2:$E$20,1)
Similarly, we can check for and add the 2's in C14:
=SUMIF($E$2:$E$20,2)
Et cetera.

2

How is it determined which group belong to what cohort? If it is predetermined, you could enter those in another table (with columns: group, cohort with a row for each group) and then the formula in C would use VLOOKUP to fetch the correct cohort for that group.
VLOOKUP could essential do this: Let's do A. Ok this is group 4, let's check in that other table ...

2

In general, VLOOKUP would be the way to go on a problem like this, and @mtone's answer describes that. It just happens that your data allows what might be an even simpler solution that doesn't require a table. The CHOOSE function picks a value from a list and your group "names" are perfectly suited to use as the index. You would build the list once inside ...

2

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.

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

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