dynamically growing sheet

[brigham white]

Is there a way to setup a spreadsheet that dynamically grows and accumulates
data based on new entries put in? I'll try to explain what I'm doing. I'm
trying to track and graph the day by day performance of several stocks based
on the percentage change from the previous day. The functionality I want is
to input the dollar value change and then then calculate the percnetage
change and graph it. But I don't know the syntax to write a formula that is
accumulative.. Thanks..

 

[Frank Kabel]

Hi
not sure what you mean with accumulative formula. But if you want a
dynamic growing chart (thats is automatically included all rows), have
a look at the following sites for examples how to set up such charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

For your formulas some assumptions:
- column A stores the dollar value
- column B the percentage change

Enter the following in the second data row for column B (assumption
cell B2)
=IF(A2<>"",(A2/A1)-1,"")
copy down and format column B as percentage

HTH
Frank

 

[brigham white]

I just meant a formula that gives a result that is not based on some static
number of cells, but based on the accumulation of more and more data put in.
It's hard to convey what I mean, but I believe you addressed it from what
you said. I haven't had time to check out the links yet, but I will shortly
and will let you know if that is what I'm after, thanks..

 

[beeawwb]

I would think it's done pretty much the same was as for defining a chart
axis, that grows week by week. I haven't seen the links, but the way I
have done this is.

Insert->Name->Define

YCMCorresp
Refers To:
=OFFSET(Stats_Chart!$A$1,7,1,1,COUNT(Stats_Chart!$B$8:$AZ$8))

Which says...

YCMCorresp is 7 down from 1, and X Nonblank cells from A, taking into
account all things between B8 and AZ8 (I have A as my labels.)

So, I can then make YCMCorresp a series on a chart, or, can do

=SUM(YCMCorresp) and have all my values added together.

Maybe you can do something with that?

Hope it helps,

-Bob

 

[Frank Kabel]

Hi
o.k. now I understand you
Lets say you want to sum alls rows in column A (assumption: no blanks
in between). Use the following formula:
=SUM(OFFSET($A$1,0,0,COUNTA($A:$A)))

or to get the last row in column A use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

HTH
Frank

 

[brigham white]

What if I wanted to sum all values following say.. row 4, instead of all
rows in the column.. what is the syntax on that.. I've been resarching this
but cannot find a solution. Maybe I'm looking in the wrong places =(.

 

[Frank Kabel]

Hi
do you want to sum all rows in lets say column A starting at row 4. If
yes, try
=SUM(OFFSET($A$4,0,0,COUNTA($A4:$A99999)))

HTH
Frank

 

[brigham white]

Thankyou, you've been quite helpfull. I program, but haven't worked in excel
before so I know little about the syntax =). Anyways, another question. I
have my sheet set to take input, one after another in a dynamically
increasing range and make calculations based on that, but I need to output
the results of that data, one after another as it's put to a similarly
increasing range. So for example, (I have it set where input is on the same
row, into the next available column) each time I input a value, at some
number of rows down I want it to output a calculated figure in that same
column. How can I output a result to some designated cell.. thanks..

 

[Frank Kabel]

Hi
if your data is entered in column A and your calculation should get
into column B enter the following in B1:
=IF(A1<>"",your calculation_formula,"")
copy down

HTH
Frank