Harlan Grove
2007-08-23 05:43:04 UTC
FTHOI, I've decided to post some spreadsheet thoughts here on a daily basis.
Who needs a blog?
In my work one of the data structures I use is trapezoidal. That is, of the
form
| 1 | 2 | 3 | 4 | 5
--+---+---+---+---+---
1 | | | a | b | c
2 | | a | b | c |
3 | a | b | c | |
4 | b | c | | |
5 | c | | | |
where the rows represent different contract periods, the columns represent
different periods from the beginning of the respective contract periods
(lags), and the 'diagonals' (a, b, c) the same calendar period. Various
summary statistics may be calculated from each column, but the data begins
and ends in different rows.
One solution is entering a formula for the first column, copying it, then
moving to the next column to the right AND UP ONE ROW and pasting there,
then CUT that cell and paste into the cell below, and repeat this process
for all remaining columns. It works, but it's tedious.
Another solution is using OFFSET. With data in B2:K11 and 5 'diagonals', the
formula for the first columns might be
=MAX(OFFSET($B$2,COLUMNS(B2:$F2),0,5,1))
which could be filled from the cell containing this formula into the cells
in the next 4 columns to the right. That works, but OFFSET is volatile. Lots
of OFFSET calls in a workbook can really slow Excel down.
A better option would be using Excel range expressions, creating ranges
using ':' as an operator. The OFFSET call above could be replaced with
=MAX(INDEX(B$2:B$11,COLUMNS(B2:$G11)):INDEX(B$2:B$11,COLUMNS(B2:$K11)))
Note: this doesn't work in OpenOffice.org Calc 2.2.0. OOo Calc returns
Err:508. However, this does work in Gnumeric 1.7.10. As for online
spreadsheets, ThinkFree, EditGrid and Zoho all can't handle it, but Google
Spreadsheet can, which comes as a big (and welcome!) surprise to me.
I no longer have 123 on any PC to which I have access, and I no longer
bother with Quattro Pro. I'd be interested to see how either of those
spreadsheets could handle this without resorting to using @@, though I have
no doubt that 123's @@ is far more efficient than Excel's volatile OFFSET
and INDIRECT.
Who needs a blog?
In my work one of the data structures I use is trapezoidal. That is, of the
form
| 1 | 2 | 3 | 4 | 5
--+---+---+---+---+---
1 | | | a | b | c
2 | | a | b | c |
3 | a | b | c | |
4 | b | c | | |
5 | c | | | |
where the rows represent different contract periods, the columns represent
different periods from the beginning of the respective contract periods
(lags), and the 'diagonals' (a, b, c) the same calendar period. Various
summary statistics may be calculated from each column, but the data begins
and ends in different rows.
One solution is entering a formula for the first column, copying it, then
moving to the next column to the right AND UP ONE ROW and pasting there,
then CUT that cell and paste into the cell below, and repeat this process
for all remaining columns. It works, but it's tedious.
Another solution is using OFFSET. With data in B2:K11 and 5 'diagonals', the
formula for the first columns might be
=MAX(OFFSET($B$2,COLUMNS(B2:$F2),0,5,1))
which could be filled from the cell containing this formula into the cells
in the next 4 columns to the right. That works, but OFFSET is volatile. Lots
of OFFSET calls in a workbook can really slow Excel down.
A better option would be using Excel range expressions, creating ranges
using ':' as an operator. The OFFSET call above could be replaced with
=MAX(INDEX(B$2:B$11,COLUMNS(B2:$G11)):INDEX(B$2:B$11,COLUMNS(B2:$K11)))
Note: this doesn't work in OpenOffice.org Calc 2.2.0. OOo Calc returns
Err:508. However, this does work in Gnumeric 1.7.10. As for online
spreadsheets, ThinkFree, EditGrid and Zoho all can't handle it, but Google
Spreadsheet can, which comes as a big (and welcome!) surprise to me.
I no longer have 123 on any PC to which I have access, and I no longer
bother with Quattro Pro. I'd be interested to see how either of those
spreadsheets could handle this without resorting to using @@, though I have
no doubt that 123's @@ is far more efficient than Excel's volatile OFFSET
and INDIRECT.