The problem is how to use variables in the formulae to specify cell ranges.
I have a spreadsheet that is periodically refreshed. It has long columns of
data that can be readily sorted. I could set up a formula to extract the
first row for each of a succession of changes to the data, ergo, the last
row, too. On the subdivisions of those sorted columns, I wanted to perform
certain mathematical operations, but use as cell range references the
variable that read the values from another cell range.
e.g., this week, data set A occupies rows 1 to 17; B is in rows 18 to 25; C
is row 26 to row 47, etc . . The sort and sub-division criteria are in
column a, so all formulae want to work on the values in, say, column b. In
the table below, column C shows the subdivisions (A - C) I'm evaluating.
I have already made a formula that would capture out put that looked like
this - pretend it is column a and b cells 15 - 17
a b c
15 1 17 A
16 18 25 B
17 26 47 C
Now, I wanted to add to a sum, determine an arithmetic mean, average, mode,
or whatever on the contents of those ranges. So I wanted to point to the
content of a15 and b15 as variables. The formula might look like this
=sum(b:val(a:15):b:val(b:15)). (Remember, column a is how we sort and
distinguish data subsets, and column b is where the values are we want to
evaluate. Even though 15 and 15 are in the formula, I don't want 15+15=20,
and even though a15=1 and B15=17, I don't want 1+17 = 18. I want the value
of the sum of the cells in column b1 through b17. For purposes of expressing
a made-up formula, I invented the function "val" for "value" to express what
I wanted. If I knew the real way to do it, I wouldn't be writing this
epistle.
The solution becomes even more valuable when I reach into another file,
leaving the evaluation formuae in a static file.
I build the evaluation formulae in one spreadsheet that points to another.
The formula-storing spreadsheet doesn't change (similar to a query definition
in Access). Then I periodically replace the file being evaluated with a
current version with the same title (fully qualified path name, just jack up
the old file and pull in the new file) , and the evaluation results are
automatically generated on the new numbers in my evaluation spreadsheet
without me having to go in and specify new ranges for operators based on that
spreadsheet content. Maybe A is 1 to 17 this week and will be 1 to 22 next
week. Maybe C is 26 to 47 this week but C is 70 to 131 next week. I have it
already set up in my evaluation spreadsheet (the one that doesn't change), to
find 1 and 17 and 1 and 22 or 26 and 47 and 70 and 131 to populate a table
(range a15:b17). I now need to perform operators on the cell ranges defined
by the formula, using these output as input to other functions. Variables in
a formula that refers to cell ranges, looking to the contents of other cells
than that within the range being operated upon. That was unclear, right?
Try this:
Next week contents of cells a15:b17 are
1 a b c
15 1 26 A
16 27 69 B
17 70 131 C
So lets learn about data set A. this week =sum(b:val(a:100):b:val(b:100))
adds cells b1:b17, but next week =sum(b:val(a:15):b:val(b:17)) adds cells
b1:b26, where 1 is the value in a:15 and 26 is the value in cell b:15.
The problem is how to use variables in the formulae to specify cell ranges