J
Joseph M. Newcomer
I have a couple spreadsheets where I'm collecting data over time (new data gets added,
say, weekly). A number of computations (max, min, sum, average, frequency [note this is an
array valued function], and graphs) need to be recomputed on the current amount of data.
So now I have, say, a dozen cells which do something on Q1..Q1532, but next week they have
to do the computation on Q1..Q1604. And six graphs, all of which also require that I
tediously update the bounds of each series. What I need is a way to allow ALL of these
computations (including all of the plot series and the frequency) to automatically extend
as my data extends, as I am losing track of which cells need to be extended as I add new
and more interesting graphs and data analyses to the basic dataset.
I have a vague memory of many, many years ago, perhaps in some other spreadsheet program
(I don't use spreadsheets very often), being able to put the maximum row number in a cell,
e.g., X7, and being able to write expressions of the form
A1..A$(X7)
but that is a syntax clearly not supported by Excel, and in any case it may not have been
what I used. But I spent about an hour today perusing the online help and could not find
anything useful (although in my opinion the organization and searchabiity of the online
docs has seriously degraded over the years). Can anyone tell me if Excel can do something
like this and if so what the syntax is like?
thanks
joe
Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm
say, weekly). A number of computations (max, min, sum, average, frequency [note this is an
array valued function], and graphs) need to be recomputed on the current amount of data.
So now I have, say, a dozen cells which do something on Q1..Q1532, but next week they have
to do the computation on Q1..Q1604. And six graphs, all of which also require that I
tediously update the bounds of each series. What I need is a way to allow ALL of these
computations (including all of the plot series and the frequency) to automatically extend
as my data extends, as I am losing track of which cells need to be extended as I add new
and more interesting graphs and data analyses to the basic dataset.
I have a vague memory of many, many years ago, perhaps in some other spreadsheet program
(I don't use spreadsheets very often), being able to put the maximum row number in a cell,
e.g., X7, and being able to write expressions of the form
A1..A$(X7)
but that is a syntax clearly not supported by Excel, and in any case it may not have been
what I used. But I spent about an hour today perusing the online help and could not find
anything useful (although in my opinion the organization and searchabiity of the online
docs has seriously degraded over the years). Can anyone tell me if Excel can do something
like this and if so what the syntax is like?
thanks
joe
Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm