How to calculate (generate) a cell reference

[LurfysMa]

I need a way to generate a cell address (row,column) from the value in
another cell and be able to use the contents of that in a calculation.

Here's my situation:

I have a table of mileage readings for my car taken at odd intervals.
Note that the "[Row]" column is the actual row number in the
spreadsheet and not part of the data. It is included because I want to
reference it later.

[Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335
14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843
15 10/29/05 52,176 1,254 111 3.65 0.30 11 344 4,126
16 12/15/05 55,338 3,162 47 1.54 0.13 67 2,048 24,573
17 5/01/06 57,767 2,429 137 4.50 0.38 18 540 6,476
18 6/30/06 58,494 727 60 1.97 0.16 12 369 4,426
19 10/12/06 62,144 3,650 104 3.42 0.28 35 1,068 12,819
20 2/02/07 65,368 3,224 113 3.71 0.31 29 868 10,421
21 3/12/07 65,719 351 38 1.25 0.10 9 281 3,374

The last column calculates the annualized miles/year based on the last
two readings. The reading are at irregular intervals, which makes it
difficult to calculate average mileage.

My idea was to add a row or two at the bottom of the table where I
would calculate the averages across enough intervals to span a year or
two. I could do the last 3 or 4 or 5 intervals and that would probably
be good enough, but what I'd like to do (maybe more for the learning
experience than the actual usefulness) is to add a new column (Row) in
which I could put the row number of the interval I want to compare
with. I could then choose an interval that is close to 1 or 2 or 3
years back and get the desired averages.

Here is an example using the data from the table above and choosing
rows that are about 1 and 2 years back. The new "Row" column is where
I would put the row number that I want to compare the last row in the
table against:

[Row] Row Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
23 16 12/15/05 55,338 10,381 452 14.85 1.24 23 699 8,389
24 13 3/13/05 48,041 17,678 729 23.95 2.00 24 738 8,857

This table was generated using actual cell references. The date cell
is "=B16", the Odom cell is "=C16", the Miles cell is "=C21-C16", and
the Miles cell is "=B21-B16".

What I need is a way to replace the "16" in each of these formulas
with the contents of cell A23. I can't seem to quite get it to work.

I think the solution is some combination of the cell() function and
the address() function, but I can't get it to work.

Using the address() function, I was able to obtain the address of the
cell:

=ADDRESS(A26,2) ==> "$B$16"
=ADDRESS(A26,2,4) ==> "B16"

Using the cell() function, I was able to obtain the contents of the
cell:

=CELL("contents",B16) ==> 12/15/05
=CELL("contents",C16) ==> 55,338

But when I try to combine these functions, I get an error:

=CELL("contents",ADDRESS(A26,2,4)) ==> Error

What am I doing wrong?

 

[LurfysMa]

I need a way to generate a cell address (row,column) from the value in
another cell and be able to use the contents of that in a calculation.

Here's my situation:

I have a table of mileage readings for my car taken at odd intervals.
Note that the "[Row]" column is the actual row number in the
spreadsheet and not part of the data. It is included because I want to
reference it later.

[Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335
14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843
15 10/29/05 52,176 1,254 111 3.65 0.30 11 344 4,126
16 12/15/05 55,338 3,162 47 1.54 0.13 67 2,048 24,573
17 5/01/06 57,767 2,429 137 4.50 0.38 18 540 6,476
18 6/30/06 58,494 727 60 1.97 0.16 12 369 4,426
19 10/12/06 62,144 3,650 104 3.42 0.28 35 1,068 12,819
20 2/02/07 65,368 3,224 113 3.71 0.31 29 868 10,421
21 3/12/07 65,719 351 38 1.25 0.10 9 281 3,374

The last column calculates the annualized miles/year based on the last
two readings. The reading are at irregular intervals, which makes it
difficult to calculate average mileage.

My idea was to add a row or two at the bottom of the table where I
would calculate the averages across enough intervals to span a year or
two. I could do the last 3 or 4 or 5 intervals and that would probably
be good enough, but what I'd like to do (maybe more for the learning
experience than the actual usefulness) is to add a new column (Row) in
which I could put the row number of the interval I want to compare
with. I could then choose an interval that is close to 1 or 2 or 3
years back and get the desired averages.

Here is an example using the data from the table above and choosing
rows that are about 1 and 2 years back. The new "Row" column is where
I would put the row number that I want to compare the last row in the
table against:

[Row] Row Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
23 16 12/15/05 55,338 10,381 452 14.85 1.24 23 699 8,389
24 13 3/13/05 48,041 17,678 729 23.95 2.00 24 738 8,857

This table was generated using actual cell references. The date cell
is "=B16", the Odom cell is "=C16", the Miles cell is "=C21-C16", and
the Miles cell is "=B21-B16".

What I need is a way to replace the "16" in each of these formulas
with the contents of cell A23. I can't seem to quite get it to work.

I think the solution is some combination of the cell() function and
the address() function, but I can't get it to work.

Using the address() function, I was able to obtain the address of the
cell:

=ADDRESS(A26,2) ==> "$B$16"
=ADDRESS(A26,2,4) ==> "B16"

Using the cell() function, I was able to obtain the contents of the
cell:

=CELL("contents",B16) ==> 12/15/05
=CELL("contents",C16) ==> 55,338

But when I try to combine these functions, I get an error:

=CELL("contents",ADDRESS(A26,2,4)) ==> Error

What am I doing wrong?

I think I found one way to handle this using the indirect() function:

=INDIRECT(ADDRESS(A26,2,4))

Is there a better way?

Why doesn't the cell() function work?

Thanks

 

[Max]

If I've guessed your intents correctly, here's a much easier approach using
INDEX/MATCH to extract the data for any 2 dates from your source table for
the comparison calcs ..

A nicely rendered sample is available at:
http://cjoint.com/?dnjCgQuJbK
Extract data from table for comparison.xls
(.. savefile.com is still down for uploading ..)

Assuming the source table is within A1:I10

We'll use an empty area below the table to set it up

Create a DV list to select the 2 dates for the comparison
Select A14:A15
Click Data > Validation
Allow: List
Source: =$A$2:$A$10
Click OK. Format A14:A15 as dates

Then place in B14:
=IF($A14="","",INDEX(B$2:B$10,MATCH($A14,$A$2:$A$10,0)))
Copy B14 across to I14, fill down to I15, to extract the data corresponding
to the 2 dates in A14:A15

Frame up your calcs as required in row 16, eg
in C16: =IF(OR(B14="",B15=""),"",B15-B14)
in D16: =IF(OR(A14="",A15=""),"",A15-A14)
etc

There, you're all ready to go. Just select any 2 dates in A14 & A15 (an
earlier date in A14, a later date in A15) and you would have the extracts and
comparisons all nicely done !

I need a way to generate a cell address (row,column) from the value in
another cell and be able to use the contents of that in a calculation.

Here's my situation:

I have a table of mileage readings for my car taken at odd intervals.
Note that the "[Row]" column is the actual row number in the
spreadsheet and not part of the data. It is included because I want to
reference it later.

[Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335
14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843
15 10/29/05 52,176 1,254 111 3.65 0.30 11 344 4,126
16 12/15/05 55,338 3,162 47 1.54 0.13 67 2,048 24,573
17 5/01/06 57,767 2,429 137 4.50 0.38 18 540 6,476
18 6/30/06 58,494 727 60 1.97 0.16 12 369 4,426
19 10/12/06 62,144 3,650 104 3.42 0.28 35 1,068 12,819
20 2/02/07 65,368 3,224 113 3.71 0.31 29 868 10,421
21 3/12/07 65,719 351 38 1.25 0.10 9 281 3,374

The last column calculates the annualized miles/year based on the last
two readings. The reading are at irregular intervals, which makes it
difficult to calculate average mileage.

My idea was to add a row or two at the bottom of the table where I
would calculate the averages across enough intervals to span a year or
two. I could do the last 3 or 4 or 5 intervals and that would probably
be good enough, but what I'd like to do (maybe more for the learning
experience than the actual usefulness) is to add a new column (Row) in
which I could put the row number of the interval I want to compare
with. I could then choose an interval that is close to 1 or 2 or 3
years back and get the desired averages.

Here is an example using the data from the table above and choosing
rows that are about 1 and 2 years back. The new "Row" column is where
I would put the row number that I want to compare the last row in the
table against:

[Row] Row Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
23 16 12/15/05 55,338 10,381 452 14.85 1.24 23 699 8,389
24 13 3/13/05 48,041 17,678 729 23.95 2.00 24 738 8,857

This table was generated using actual cell references. The date cell
is "=B16", the Odom cell is "=C16", the Miles cell is "=C21-C16", and
the Miles cell is "=B21-B16".

What I need is a way to replace the "16" in each of these formulas
with the contents of cell A23. I can't seem to quite get it to work.

I think the solution is some combination of the cell() function and
the address() function, but I can't get it to work.

Using the address() function, I was able to obtain the address of the
cell:

=ADDRESS(A26,2) ==> "$B$16"
=ADDRESS(A26,2,4) ==> "B16"

Using the cell() function, I was able to obtain the contents of the
cell:

=CELL("contents",B16) ==> 12/15/05
=CELL("contents",C16) ==> 55,338

But when I try to combine these functions, I get an error:

=CELL("contents",ADDRESS(A26,2,4)) ==> Error

What am I doing wrong?

I think I found one way to handle this using the indirect() function:

=INDIRECT(ADDRESS(A26,2,4))

Is there a better way?

Why doesn't the cell() function work?

Thanks

 

[LurfysMa]

I need a way to generate a cell address (row,column) from the value in
another cell and be able to use the contents of that in a calculation.

Here's my situation:

I have a table of mileage readings for my car taken at odd intervals.
Note that the "[Row]" column is the actual row number in the
spreadsheet and not part of the data. It is included because I want to
reference it later.

[Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335
14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843
15 10/29/05 52,176 1,254 111 3.65 0.30 11 344 4,126
16 12/15/05 55,338 3,162 47 1.54 0.13 67 2,048 24,573
17 5/01/06 57,767 2,429 137 4.50 0.38 18 540 6,476
18 6/30/06 58,494 727 60 1.97 0.16 12 369 4,426
19 10/12/06 62,144 3,650 104 3.42 0.28 35 1,068 12,819
20 2/02/07 65,368 3,224 113 3.71 0.31 29 868 10,421
21 3/12/07 65,719 351 38 1.25 0.10 9 281 3,374

The last column calculates the annualized miles/year based on the last
two readings. The reading are at irregular intervals, which makes it
difficult to calculate average mileage.

My idea was to add a row or two at the bottom of the table where I
would calculate the averages across enough intervals to span a year or
two. I could do the last 3 or 4 or 5 intervals and that would probably
be good enough, but what I'd like to do (maybe more for the learning
experience than the actual usefulness) is to add a new column (Row) in
which I could put the row number of the interval I want to compare
with. I could then choose an interval that is close to 1 or 2 or 3
years back and get the desired averages.

Here is an example using the data from the table above and choosing
rows that are about 1 and 2 years back. The new "Row" column is where
I would put the row number that I want to compare the last row in the
table against:

[Row] Row Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr
23 16 12/15/05 55,338 10,381 452 14.85 1.24 23 699 8,389
24 13 3/13/05 48,041 17,678 729 23.95 2.00 24 738 8,857

This table was generated using actual cell references. The date cell
is "=B16", the Odom cell is "=C16", the Miles cell is "=C21-C16", and
the Miles cell is "=B21-B16".

What I need is a way to replace the "16" in each of these formulas
with the contents of cell A23. I can't seem to quite get it to work.

I think the solution is some combination of the cell() function and
the address() function, but I can't get it to work.

Using the address() function, I was able to obtain the address of the
cell:

=ADDRESS(A26,2) ==> "$B$16"
=ADDRESS(A26,2,4) ==> "B16"

Using the cell() function, I was able to obtain the contents of the
cell:

=CELL("contents",B16) ==> 12/15/05
=CELL("contents",C16) ==> 55,338

But when I try to combine these functions, I get an error:

=CELL("contents",ADDRESS(A26,2,4)) ==> Error

What am I doing wrong?

I think I found one way to handle this using the indirect() function:

=INDIRECT(ADDRESS(A26,2,4))

Apparently, I don't even need the address() function:

=INDIRECT("B"&A26)

also works.

 

[LurfysMa]

If I've guessed your intents correctly, here's a much easier approach using
INDEX/MATCH to extract the data for any 2 dates from your source table for
the comparison calcs ..

A nicely rendered sample is available at:
http://cjoint.com/?dnjCgQuJbK
Extract data from table for comparison.xls
(.. savefile.com is still down for uploading ..)

Assuming the source table is within A1:I10

We'll use an empty area below the table to set it up

Create a DV list to select the 2 dates for the comparison
Select A14:A15
Click Data > Validation
Allow: List
Source: =$A$2:$A$10
Click OK. Format A14:A15 as dates

Then place in B14:
=IF($A14="","",INDEX(B$2:B$10,MATCH($A14,$A$2:$A$10,0)))
Copy B14 across to I14, fill down to I15, to extract the data corresponding
to the 2 dates in A14:A15

Frame up your calcs as required in row 16, eg
in C16: =IF(OR(B14="",B15=""),"",B15-B14)
in D16: =IF(OR(A14="",A15=""),"",A15-A14)
etc

There, you're all ready to go. Just select any 2 dates in A14 & A15 (an
earlier date in A14, a later date in A15) and you would have the extracts and
comparisons all nicely done !

Thanks, Max. I think you have very correctly guessed my intentions.
I'm not sure, because your solution is beyond my skills at the moment.
I will play with it and see if I can figure it out.

Thanks for the suggestion. It's always great to learn a new approach.

 

[LurfysMa]

If I've guessed your intents correctly, here's a much easier approach using
INDEX/MATCH to extract the data for any 2 dates from your source table for
the comparison calcs ..

A nicely rendered sample is available at:
http://cjoint.com/?dnjCgQuJbK
Extract data from table for comparison.xls
(.. savefile.com is still down for uploading ..)

Assuming the source table is within A1:I10

We'll use an empty area below the table to set it up

Create a DV list to select the 2 dates for the comparison
Select A14:A15
Click Data > Validation
Allow: List
Source: =$A$2:$A$10
Click OK. Format A14:A15 as dates

Then place in B14:
=IF($A14="","",INDEX(B$2:B$10,MATCH($A14,$A$2:$A$10,0)))
Copy B14 across to I14, fill down to I15, to extract the data corresponding
to the 2 dates in A14:A15

Frame up your calcs as required in row 16, eg
in C16: =IF(OR(B14="",B15=""),"",B15-B14)
in D16: =IF(OR(A14="",A15=""),"",A15-A14)
etc

There, you're all ready to go. Just select any 2 dates in A14 & A15 (an
earlier date in A14, a later date in A15) and you would have the extracts and
comparisons all nicely done !

Thank you so much for that tip. I was able to find lots of examples
online and have a version of what you suggest working.

You are correct that this is far superior to my approach.

Not only does it avoid most of the problems I was encountering, but it
will find the closest match. It does not require an exact match. Since
my data is indexed by date (and also by mileage, if I decide that is
useful), I can get it to select a date that it at least n months (or
years) back. Thaty way, I don't have to choose the entries at all and
it updates itself when I add a row (if I am careful about how it do
it).

I am in your debt.

 

[Max]

Glad it helped, LurfysMa.
You're welcome.