When value is NOT Null
 

I started a formula that I thought was fairly simple. However, I cant
seem to make it work.

Basically, I have a spreadsheet that gives me the rate of return on my
investment. Each day I enter the value of my investment in the cell
below the previous days value. So the formula would look like this:
(C226-C35)/C35
The problem is I have to change the row reference (C226) to the cell
containing the new value (say C227) each day (I dont like doing that).


Is there a way to have my formula automatically update to the newly
added value each day?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile: http://www.excelforum.com/member.php...o&userid=27906
View this thread: http://www.excelforum.com/showthread...hreadid=474043

Use:

=(OFFSET(C34,COUNT(C34:C10000),0)-C35)/C35

remember, there should be nothing in the column C34 onwards, only the prices
should be there.

Mangesh


"sbigelow" wrote in
message ...

I started a formula that I thought was fairly simple. However, I cant
seem to make it work.

Basically, I have a spreadsheet that gives me the rate of return on my
investment. Each day I enter the value of my investment in the cell
below the previous days value. So the formula would look like this:
(C226-C35)/C35
The problem is I have to change the row reference (C226) to the cell
containing the new value (say C227) each day (I dont like doing that).


Is there a way to have my formula automatically update to the newly
added value each day?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile:
http://www.excelforum.com/member.php...o&userid=27906
View this thread: http://www.excelforum.com/showthread...hreadid=474043

Sorry, make it:

=(OFFSET(C34,COUNT(C35:C10000),0)-C35)/C35

Mangesh



"Mangesh Yadav" wrote in message
...
Use:

=(OFFSET(C34,COUNT(C34:C10000),0)-C35)/C35

remember, there should be nothing in the column C34 onwards, only the
prices
should be there.

Mangesh


"sbigelow" wrote
in
message ...

I started a formula that I thought was fairly simple. However, I cant
seem to make it work.

Basically, I have a spreadsheet that gives me the rate of return on my
investment. Each day I enter the value of my investment in the cell
below the previous days value. So the formula would look like this:
(C226-C35)/C35
The problem is I have to change the row reference (C226) to the cell
containing the new value (say C227) each day (I dont like doing that).


Is there a way to have my formula automatically update to the newly
added value each day?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile:
http://www.excelforum.com/member.php...o&userid=27906
View this thread:
http://www.excelforum.com/showthread...hreadid=474043

Yes there is a way, i just figured out how to do it myself!

If you have an equation that sums an entire column like =SUM(A1:A50)
and you need it to keep updating, just insert a row within the range of
rows 1 to 50, and the formula will automatically update to =SUM(A1:A51)


--
Faz
------------------------------------------------------------------------
Faz's Profile: http://www.excelforum.com/member.php...o&userid=27830
View this thread: http://www.excelforum.com/showthread...hreadid=474043

Faz,

the OP will be simply entering the new value at the end of the column, and
not inserting a new row everytime.

Mangesh




"Faz" wrote in message
...

Yes there is a way, i just figured out how to do it myself!

If you have an equation that sums an entire column like =SUM(A1:A50)
and you need it to keep updating, just insert a row within the range of
rows 1 to 50, and the formula will automatically update to =SUM(A1:A51)


--
Faz
------------------------------------------------------------------------
Faz's Profile:
http://www.excelforum.com/member.php...o&userid=27830
View this thread: http://www.excelforum.com/showthread...hreadid=474043