Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preventing NULL showing as 0 on a pivot chart using Office Web Component. | Charts and Charting in Excel | |||
Null points in a line chart | Excel Discussion (Misc queries) | |||
Multipying columns that contail null values | Excel Discussion (Misc queries) | |||
Charting should not plot NULL as a zero | Charts and Charting in Excel | |||
how to enter a null cell value in a formula | Excel Worksheet Functions |