How do you make formulas "relative" in a Excell spreadsheet?

[Gary Allan]

Gosh I really dislike the mentality at MS more and more.

I can setup a spreadsheet in Lotus in about 5 minutes. With never reading the manual and only getting a fundamental understanding of the syntax of the formulas ..I can basically highlight from one corner of the spreadsheet to the other diagonally ..and voila~ done. All the horizonal cells will add left to right ..total at the end ..and all the columns will add top to bottom ..all with entering two formulas in two cells ..or at least it seems that easy. One based on column (ie Ax..Zx) and one based on line (2A..99A). The rest sorta figures itself out.

Now I don't have Excell ..but it appears to be the (forced upon us) spreadsheet of choice. I have an Excell editor/viewer from openoffice.org. I can enter each individual formula for each cell ..but if I "drag and drop" the formula ..it doesn't have the brains to change the parameters for that cell.

As you can imagine, this makes longer/frequent entries (like fuel economy - immediate/lifetime type configurations cumbersome.

For example, I have two columns for fuel and miles (trip odo)...that calc is easy to put in a third column for the average. Now I have to drag and drop that formula (A/B or =SUM(A/B)) to each individual cell desending. This is irritating enough ..but livable. When I want to calculate an advancing lifetime average, however, the only way I get it to work is with =SUM(A2:AXXX)/SUM(B2:BXXX) ..but when I go to the next desending cell ..I have to manually change the "2" (A2) to the next desending cell's line. Now I think that I can just use =Average(A2:Axxx)/(B2:Bxxx) ..but that won't show the difference in evolution in lifetime average. That is, the first entries may result in 19.99 mpg in C2 ..while the next desending row will have all SUM of all A's / SUM of all B's and display the resultant number.

There has got to be some simple way to make this easier. Unfortunately I have no reference material.

Thanks in advance!

 

[tweeker43]

to make an address within a cell absolute, select the address in the formula and press F4.

the address will cycle through something like:

$A$1 -> A$1 -> $A1 -> A1.

this works on a range as well (A1:B6).

 

[michaelc80]

I can't say for the Open Office viewer, however in Excel simply typing in the formula will make it relative. Placing $ in front of the cell references will make it absolute.

=A1+A2 makes the entire formula relative.
=$A1+A2 makes the first column reference absolute.
=$A$1+A2 makes the first column and row reference absolute. Etc....

 

[Gary Allan]

Thank you for the replies. Okay (and if you're willing) ..how do I make a relative formula "limited" without manual entry?

That is I want SUM(A2:A7)/SUM(B2:B7) displayed in C7 ..but I want C8 to disply SUM(A2:A8)/SUM(B2:B8) without doing each formula for each "C" line??

Or am I doomed to manually modifying each C cell for each line??

 

[tom slick]

I use openoffice. try putting the correct formula in 2 sets of cells then highlight both of them then drag as needed.
a simple example is in you put a 1 in A1 the drag it to A2 it will copy the 1 into A2 then a 1 to A3 etc. if you put a 1 in A1 then a 2 in A2, then highlight both and drag it will progressivly count up for you so you'll get a 3 in A3, a 4 in A4 etc.

if hope this makes sense. basically you have to show it what you are trying to do, then it will try to copy your action.

 

[TooManyWheels]

Gary - If I correctly understand what you want to do, you are wanting to leave the totals in place, but be able to insert a last line of data when you fill your tank. If that is true, you leave the dollar sign on the starting cells, making them absolute, but take the dollar sign off the ending cells, making the address relative to the position of the formula. As you add a line and move the formula, the ending cells referenced in the formula change automatically.

Is that what you are asking?

P.S. - I don't know how this works in the viewer/editor, but in Excel proper there are two ways to drag/extend a formula. One is by just grabbing the edge of the cell and moving it down. The other is by simultaneously holding the Control key while you drag. In some circumstances this increments the cell addresses by one, although I don't know exactly what those are, or how this feature interacts with absolute addresses.

[ April 26, 2005, 06:03 PM: Message edited by: TooManyWheels ]

 

[tweeker43]

put your averages directly below the data. then, when it's time to insert new data you can right-click on the bottom row and choose insert. as soon as you enter new data it will recalc for you.

you can insert multiple rows at once this way as well.

for instance, if your average is in row 25, right click on the "25" at the left of your screen, select insert. enter your data.

this is pretty low tech.

 

[Razl]

quote:

---

Originally posted by Gary Allan:
That is I want SUM(A2:A7)/SUM(B2:B7) displayed in C7 ..but I want C8 to display SUM(A2:A8)/SUM(B2:B8) without doing each formula for each "C" line??

Or am I doomed to manually modifying each C cell for each line??

---

If I understand you correctly, you want each C cell to equal the sum of column A down to the correspond C cell row divided by the sum of column B down to the correspond C cell row . Using your example, you want the next C cell, that is C9, to equal Sum(A2:A9)/Sum (A2:B9) and so one with C10, C11, C12, etc. IF so here's how I'd do it.

For your first C value, C7, type in =SUM($A$2:A7)/SUM($B$2:B7). Bring your cursor to the right hand corner of C7 until the white + cursor changes to a black bold +. There's even a little black square in that corner indicating that you can paste referenced formula. Now, with the black + cursor, click and drag as far down the C column that you want. Let's say you go down to C20, the value of that cell will be =SUM($A$2:A20)/SUM($B$2:B20).

Hope this was what you were looking to do

 

[Gary Allan]

Bravo!!!! Everything worked ..right down to the little square thingy.

You gotta love the information age!

Thanks

 

[Razl]

Any time Garry. Finally...I was able to return a favor to you, a highly respected member

I use Excel on a daily basis at work (insurance is data-inherent). If you have any more questions with Excel, send me a PM and I'll try to help you out the best I can.