CODEWITHSUNDEEP
excelexcel-formulaiterationcell
Zdeněk Bužga
Zdeněk Bužga

Reputation: 17

Copy cell formula with iteration

I am trying to solve a small problem. I have two rows with numbers and empty cells.

For example:

| 2005 | 2005 |      |      |
|      | 2007 |      | 2015 |

I need to create formula which will be located in the whole second row. If there is a number in first row then it counts number 2 to it and write result into the cell bellow. If cell in first row is empty and there is a number in the second row then the number remain unchanged. If there is no number in both cells, both will remain completely empty.

=IF(A6="";IF(ISNUMBER(A7);A7;"");A6+2)

I have this so far and it is almost working but when I copy formula by draging, it will erase numbers from second row if there is no number in the first row.

I do not know why it is not working. I allowed iterative calculation. I am using Microsoft Excel 2013.

Thanks for help.

Upvotes: 1

Views: 235

Answers (1)

Daniel Möller
Daniel Möller

Reputation: 86610

You cannot have a formula and a value in the same cell.

If you add a formula, for instance, where 2007 is, it will surely erase 2007. There is nothing you can do about it.

But you can create a new table below and do the following:

_____|   A  |  B   |  C   |  D   |
6    | 2005 | 2005 |      |      |
7    |      | 2007 |      | 2015 |
8    |      |      |      |      |
9    | =A6  | =B6  | =C6  | =D6  |
10   |form. |form. |form. |form. |

Use your formula where form. is written.

Upvotes: 0

Related Questions