h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

I have a question about formulas.

If you have two columns of numbers and you want to add each pair together you simply type =A1+B1 in cell C1 and it'll add them together for you. If you then want it to do that all the way down the column you hit CTRL+C and then paste it into each of the cells in column C, and the program very cleverly amends each formula to read A2+B2; A3+B3; A4+B4, and so on all the way down the column. Very handy, that. Saves a lot of time.

But what about when you're trying to work out percentages and you've got three cells-worth of numbers?

I have two columns (D3-D28 and E3-E28). Column D is sales figures for each item and is summed in cell D29. I want each relevant cell in column E to represent the percentage of total sales for each item, so the formula for cell E3 would be =(D3/D29)*100

But if I copy and paste that into cell E4 the program (Open Office Calc) amends it to =(D4/D30)*100 which returns an error because there's nothing in cell D30, and if I paste it into cell E5 it wants to go =(D5/D31)*100. It's adding one to the number of both cells in the formula, but I want it to stick on cell D29, which is the sum for the D column, so the formula for each cell in column E would read:
=(D4/D29)*100
=(D5/D29)*100
=(D6/D29)*100
=(D7/D29)*100
=(D8/D29)*100
=(D9/D29)*100
etc, when I paste.

Is there a way to do that without laboriously having to type out each formula in each cell? Am I making myself as clear as mud?

Replace D29 by $D$29 in your first cell before copying and pasting...

The dollar sign turns a reference from a relative one (which adjusts itself to wherever you paste it) to a static one which always points at the same cell.

FormulaE.

>>FormulaE.

Bullshit. Yes, if we're speaking Latin, the only plural is 'formulae'. But in English, either formulas or formulae are possible. See also forums, symposiums, octopuses, indexes. Any pedant who insists otherwise should be berated for using 'incorrect' pluralsm for other loan words such as igloo, typhoon, wallaby, bungalow, pizza, opera...

(Yes, opera are already plural)

A Cambridge mathematician phoned his colleague one Sunday:
'Come round at once. I wish to discuss certain conundra concerning maxima and minima in the oscillations of pendula.'
His colleague replied:
'Surely we can find something better to do on a Sunday than sitting on our ba doing sa?'


On the Excel, btw...yes, the $ sign means that when you C&P something, it doesn't update the reference. If you start with $A1, it will always look at ColumnA (A1, A2, A3...etc.) A$1 will make it always look at Row 1 (A1, B1, C1...etc.) A$1$ means it will always look at cell A1.

I works Thanks guys, you've no idea of the time you've saved me - I have to do weekly spreadsheets going back to the beginning of the year. I looked through the help file but couldn't find anything relevant. Maybe I just wasn't looking in the right places.

And keep saying 'formulas', if only to piss the Grammar Nazis off.

Another tip you might find useful, then. If your data is spread across different worksheets, you can refer to one from another.

Eg =A1 + 'worksheet name'A1

I rarely do, but thanks for the tip - definitely worth knowing.

However, /formulae/ is a nice word, as is /millenia/. I just like them.

TRiG.

Out of interest, when you say 'copy and paste', are you actually copying and pasting? Because you can just grab the plus sign in the corner of the cell and drag it over multiple cells for the same effect.

Once I found out that I couldn't copy and paste I started doing this: =/<click on second cell&gt*100 which is still quicker than typing out the address of each cell, but a lot slower than CTRL-V, CTRL-V, CTRL-V, CTRL-V, CTRL-V etc.

Can you explain how dragging would work with this formula?

Sorry, that should be =<(click on first cell>/<click on second cell&gt*100

You start by putting =D3/$D$29 into your cell E3. Then, you move the mouse over the blob in the bottom right of cell E3, until your mouses cursor turns into a plus sign. Then, click and drag down until you've highlighted all the cells you want the formula to be adapted for, release the mouse button and they should all be filled.

Oh my

I think I love you

>> However, /formulae/ is a nice word, as is /millenia/. I just like them.

Not to mention octopodes, stadia, oxen and sheep.

Plurals are fun. No harm in that.

How about sheeple?

A couple of days ago I the auto-complete on Visual Studio recommended a variable name: moduleses.

>>Plurals are fun. No harm in that.

No harm whatsoever. But the Latin and Greek ones are optional and the Anglicised variants shouldn't be corrected or derided. And *don't* get me started on whether 'data' is singular or plural!


Another way of avoiding the CtrlVCtrlVCtrlVing...

Select the cell you're copying and the adjacent cells in the row or column that you're copying into. Then Edit...Fill...Down (or Left, Up, Right)

I know I'll need to know this one day so should pay attention.

I've got formulae/ formula in my head to the tune of Valderie

That works too. And I might have found a use for copying between worksheets.

Not just copying, remember...referencing.

I don't know what kind of thing it is you're doing...but you can do stuff like having hourly rates for work one one worksheet...then another where you use those rates in various calculations....and a third one where you don't have any of the calculations, but use it to summarise the important totals.

Eg...

On the calculation one, you might have:
Total labour cost: = B12*'hourly rates'J19

Then on the summary sheet...say if on the calculation worksheet you've summed lots of values in a column and put them in cell M16. You just need:
Total: ='calculations'M16