Anybody with a bit of MS excel savvy?

[grcorp]

I've been pulling data and maintaining it in this manner...

-----------a-----------------b-----------------c-------------d
1--Last Sold Price----Last Sold Year----Sold Price----Sold Year
2--$100,000-------------2006-----------$200,000--------2009
3--$50,000---------------2001-----------$80,000---------2009
4--$200,000-------------2004-----------$300,000--------2008

What I need to determine is the difference between the sold price in dollars, and as a percentage.

Obviously, to determine the percentage, I must first determine the difference.

The formula that's been working for me is =sum(c2-a2). Should I have applied that formula to cell e2 in this example, the end result would have been $100,000.

The issue with this is that I must constantly replace the number for the row, and want to know what the correct variable to add is to factor the row of the current cell into the calculation.

Logical variable attempts such as =sum(c$-a$), =sum(c*-a*), etc. did me no good. Neither did inserting row() after the letter

i.e. - "=sum(c(row())-(a(row())"

Any suggestions would be VERY much appreciated!

Edit: Please excuse all the hyphens - the spaces did not format correctly thus appearing the table to appear all incorrect.

 

[INFORG]

If you simply cut and paste the formula into subsequent cells in the E column, Excel will automatically change the row reference. You don't even need the SUM function, just =C2-A2 will suffice.

 

[randomo]

If you simply cut and paste the formula into subsequent cells in the E column, Excel will automatically change the row reference. You don't even need the SUM function, just =C2-A2 will suffice.

That's true. Also, when you say "as a percentage", I assume you want to know what percent the "sold price" is of the "last sold price". To do that in cell f2, for example, use "=c2/a2", then right click on the cell, choose Format Cells, and under the Number tab, choose Percentage.

Once you have set up cells e2 and f2, just highlight them, right click, choose Copy, and drag down from e2 to the last row where you want those formulas to appear.

 

[INFORG]

That's true. Also, when you say "as a percentage", I assume you want to know what percent the "sold price" is of the "last sold price". To do that in cell f2, for example, use "=c2/a2", then right click on the cell, choose Format Cells, and under the Number tab, choose Percentage.

Once you have set up cells e2 and f2, just highlight them, right click, choose Copy, and drag down from e2 to the last row where you want those formulas to appear.

I believe the formula for the percentage would be =E2/A2 if you are looking for the change percentage of initial sales price. Format the cell as Randomo said.

 

[grcorp]

This is all assuming that "Show formulas" under the "Formulas" tab is showing.

Didn't work without it, but it worked like a charm once I made use of it.

I literally spent hours scouring the search engines and hacking away at this myself. Thanks for the help guys!