Spreadsheet help, please.

[tommag]

I've always kept my books on paper, but have been trying to use a spreadsheet and not figuring out how to do what I want. I figured this would be simple for some of you, and am not finding what I need in youtube.
I have my expenses in columns, and am trying to figure out how to set up a column to automatically calculate cost/mile when I enter expense items.
So far, I have to use the formula button on each row to do this, but there must be a way to set up a row that automatically divides each expense item by the miles. I tired highliting the entire column and using the formula b/g28 (b being the amount column, and g28 containing the monthly mileage) This does nothing, but I think I'm close, just not sure what I need to do to make the magic happen. I'm using open office spreadsheet program.
Once I learn how to do this, I imagine it would translate to percentage of revenue, etc. I'm sure it will be so obvious that I'll feel silly, but I can't figure it out on my own.
Thanks in advance. Tom

[Alvarez Kelly]

What software package are you using?

[tommag]

I'm using open office, not sure which version, but only downloaded it two weeks ago, so I guess it's the current version.

[Omega]

Put b/$g$28 in the field to the right of the expense. Copy that field to the rest by highlighting the field with the formula and dragging the lower right corner down. The $ sign keeps that cell reference locked while changing b to c, then d etc on the way down. Most datasheets use the same shortcuts, but may be different, if that don't work let me know maybe you can email me the sheet and I'll knock it out for you.

[tommag]

Thanks, Omega.
I tried that and it puts #NAME? in the cell. I'll try it with excell and see if it works there.
Oops, guess I don't have it installed. Microsoft wants 6.99 a month to use it.

[Omega]

It must not like the $, try removing it.

[Chihuahua Floyd]

You may need to put = in front of the formula so the computer knows it is a formula and not text.
CF

[Omega]

Yes, sorry figured that was a given. Should be =b/$g$28 ; according to the calc wiki, it does use $ to reference an absolute cell reference.

[tommag]

I had originally tried highlighting the column and putting in the formula, that didn't work. I put in the = sign, and still got the #name?.
Frustrating, I can do the operation for each line, just can't get the whole column to calculate automatically.

[tommag]

The = automatically gets inserted when using the formula button.

[Minerat]

Formula should be =b(Cell# you want to divide)/$g$28 so the formula =B1/$G$28 would divide the constant G28 in to cell B1.

The "$" in the number, locks that cell so if you copy the formula to another cell the G28 sell will always be used in the formula. The B cell is a variable that changes automatically as you copy the formula into another cell. Fore sake of argument lets assume this formula is in B2 then it will always divide the number in the cell above by the number in G28, i.e. copy formula to C3 then formula becomes =C2/$G$28 or into R31 then formula becomes =R30/$G$28 becomes the formula. If you then right click on the cell and pick properties you can pick the format of the answer number such as percentage or a fixed 2 decimal number.

What you will do copy this like you describe, is pick the cell with this formula and click the right mouse button and pick COPY. Then go to the row you want to copy it to and point at the first cell and hold the left button down and drag the pointer across the cells you want to copy the formula to , this will highlight the range of cells in the row. Once you have them highlighted then let off the left button pick the right button and use the left button to pick Paste. This will copy the formula into all of the cells you highlighted. This only works on rows. You can use this same highlight trick to format the cells.

If you want columns then in D1 use =C1/$G$28. This works in excel and seems to be the standard for most spreadsheet programs based on windows. Hope it helps.

[tommag]

Okay, I tried it again. SUCCESS!!!! Thanks Omega, Chihuahua Floyd, and Minerat!

[smoked turkey]

This thread and its conclusion is another reason why I love this place.

[runfiverun]

and was so far over my head....

[tommag]

and was so far over my head....

Mine, too! That's why I came here looking for expertise. From skinning a buffalo to flint and steel to rocket science, there's someone here who can explain how.
I just put my first 5 months books on spreadsheet in around an hour, and it is WONDERFUL! I'm starting to think these computers might be more than a fad.

[Alvarez Kelly]

Don't forget to BACK UP YOUR DATA!

[runfiverun]

most definitely back it up.
If you put in the work to build it you don't want something to happen to it.

[tommag]

Back up? Oh yeah, I remember, that's what I get around to after a crash!
I use a thumb drive for what little I actually create on a computer. Thanks for the reminder, guys!

[ProfGAB101]

I do a full system backup every quarter right after I file my quarterly sales tax returns. Smaller backups happen bi-weekly.

And I'm lazy too - I use OOo to calc all those sales tax figures so I know how much to render unto Caesar, and the other little Napoleon's demanding tribute.