## Hot answers tagged google-spreadsheets

49

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

31

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

9

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

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

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

3

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

2

On cell B3 put this:
=$B$2*EXP(-A3/1000)+230
This formula uses a product of:
The initial price $B$2: 2500
And the decaying factor: EXP(-A3/1000)
plus a floor factor to avoid getting to zero (230).
Now, drag this to copy to all the rows.
The results are:
Count Price
50 2500
100 2492
500 1746
1000 1150
2000 ...

2

The algebraic formula for exponential decay is a•ekt where k is less than zero.
In your example, a is 2526.78 (your price for 0 count), t will be column A. For k here is the formula
=-LN(2526.78/300)/10000
I would place your high value in C2, your low value in D2 and the corresponding high count into E2, that way you only need to change then there to ...

2

This works in Excel 2010. I'm not sure if it'll work in Google sheets.
Select the range for which you want conditional formatting.
Add conditional formatting using the following formula: (be sure to change the row from 1 to whatever the first row of your selected area is)
=AND($D1<TODAY()+7,ISBLANK($I1))
Select whatever formatting condition you want ...

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