Incrementing a Column in a formula

**David Brown** · 03-26-2013, 07:24 PM

Hello

I am trying to pick-up a time series that goes across the sheet (horizontal range), when a condition is triggered. In the example formula, that is when I228=1. When the condition is triggered, which could happen anywhere across the sheet and which could happen at a different position to the series commencing at $I$214, I want $B2*$C2 to be multiplied by the number in the time series. The problem is that when I copy across, at the first point at which the condition is met and the number $B2*$C2 would otherwise be returned, instead a Name Error occurs.

The time series increments in columns of +1, where the first number of the series which is to be multiplied by $B2*$C2 is in fact $I$214.

Cell I2=IF(I228=1,$B2*$C2*OFFSET($I$214,col()+1,0),0)

Can anyone help?

Thanks/David

**Pete_UK** · 03-26-2013, 07:29 PM

COL is not a function in Excel - I think you want COLUMN().

Hope this helps.

Pete

**Jonmo1** · 03-26-2013, 07:34 PM

There is no col() function. It's column()

**David Brown** · 03-27-2013, 01:57 AM

Hello

Simple worked example attach. Refer Cell I2 coloured yellow.

IF(I228=1,$B2*$C2*OFFSET($I$214,COLUMN()+1,0),0)

Thanks to both contributors for comments regarding COL() vs COLUMN(). I had a moment of idiocy.

However, I have tried that and the formula does not work so I am obviously making an error.

This formula needs to be copied across such that it picks-up a time series [I204:AB204] that goes across the sheet (horizontal range), when a condition is triggered (1/0) from the bottom schedule in attached example. When the condition is triggered, which (i) could happen anywhere across the sheet and (ii) which could happen at a different position to the series commencing at $I$214, I want $B2*$C2 to be multiplied by the correct number in the time series [I204:AB204] starting at the beginning of the time series when the 1/0 condition is satisfied. The formula does not allow me to do this. It blanks out the cell.

Assistance greatly appreciated.

Best / David

**Ace_XL** · 03-27-2013, 02:13 AM

Perhaps you need to switch rows and columns..
=IF(I228=1,$B2*$C2*OFFSET($I$214,0,COLUMN()+1),0)

Although I feel, this would give you the desired results
=IF(I228=1,$B2*$C2*INDEX($I$214:$AB$214,1,COUNTIF($I228:I228,1)),0)

Copy across