Dragging formulas across - Increment columns by more than 1

Question

I can't seem to find anything similar that's already been asked (they all relate to incrementing row numbers rather than columns)

I'm looking to drag a formula across horizontally and have the columns increment by 2

E.g. B1-A1, D1-C1, F1-E1...

Thanks!

Answer 1

You'll need to have a value in cell A1 and B1 for the following to work.

For my testing I put the number 1 in A1 and B1.

Try this in Cell C1:

=IF(MOD(COUNT($A$1:B1),2)=0,COLUMN(B1),IF(B1<>A1,B1,A1))

Here's what you should see when you drag that formula across:

A B C D E F G H I J K  L  M  N
1 1 2 2 4 4 6 6 8 8 10 10 12 12

And this is what the formula does:

• The MOD(COUNT() part of the formula counts the cells to the left of it, and if they are a multiple of 2, the value changes.
• I've left the value to change to (the 'new' value) as the COLUMN() number for the cell before, just for example's sake. but you can change this part.
• The last IF statement at the end checks if the cell before is equal to the cell before that, (eg. Is CELL C1 equal to CELL B1) and if they are not equal, it will give the cell before as a value (the 'copy' value).