0

I have a table, it has a few columns

Table

It has formulae like this:

Table formulae

And I would like to fill more pairs of columns like this:

Filled Table

Column "A 2" has the same formula as "A 1", but with the reference offset by 2 columns. Or generally "A i" & "B i" refer to "B (i-1)".

I've tried selecting columns A1 & B1 and dragging right, also using copy & paste. The problem is, when you drag a pair of columns in a table, the references offset by 1 cell. If I drag a pair of columns that use normal cell references rather than table indexing, the cells offset by 2 as I want.

I'd just like to add pairs of columns to the table, where the formulae point to the previous pair (so all their references need to offset by 2 table columns). What's the best way to achieve this?

My real table has many rows and many columns, and I'd prefer not to use Offset or switch to non-table style cell references ([@[blah]]) if possible (for performance and readability respectively)

Greedo
  • 4,967
  • 2
  • 30
  • 78

1 Answers1

1

You can always use an IF to choose which formula to use:

=IF(ISODD(COLUMN(A1)),[@[B 0]]+1,[@[B 0]]^2)

Where the A1 is only there as a counter.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thanks, this seems like a great unproach; but unfortunately because Excel evaluates both arguments for `IF`, I don't think it will be very performant (my actual data has 5 columns to copy, not just a pair, so every cell would evaluate 5 formulae). – Greedo May 01 '20 at 07:46
  • The formula IF does not evaluate both arguments, it only evaluates the condition and the argument that the condition requires. `IIF` in vba does both arguments but not the worksheet function `IF`. and with 5 I would use CHOOSE(), which also does not evaluate all, only the one chosen. – Scott Craner May 01 '20 at 13:40