Search code examples
excelexcel-formulaautocompleteautofill

Excel formula with multiplication that increases by 1 for every column it is in

I have a formula in one column

=IF(AND($B2+D2>2023,$B2+D2<=2122),($B2*2)+$A2,0)

and in the column beside it, the formula is

=IF(AND($B2+E2>2023,$B2+E2<=2122),($B2*3)+$A2,0)

I am hoping to modify the formula so that it automatically increases the multiplication value by 1 in every column I drag it over to.

I figured it would automatically increment, but when I dragged it over the ($B2*2) portion remained the same. I tried to add a dollar sign to see if that would work and the formula was incorrect. I have had to drag the formula over and change the multiplier manually, but there are many columns potentially.

Solution

  • Just use COLUMN() which returns a Long of the column index number:

    ($B2*COLUMN(B2))

    so in the first cell put:

    =IF(AND($B2+D2>2023,$B2+D2<=2122),($B2*COLUMN(B2))+$A2,0)

    and drag it over. The COLUMN reference will change and the multiplier will change as it is drug. And no helper row is needed.