Microsoft Excel Mac 2011: How to get the cell below value, and skip a row ?

[vpatel93]

Hi guys, I have a sheet like:

SKU	price
H100L200-245-PK10	1.09
H100L200-245-PK100	10.62
H100L245-290-PK10	1.37
H100L245-290-PK100	13.26

<tbody>
</tbody>

Now, in simple human logic, I need pack of 10 to take the price of it's 100 pack version. So, cell b2 (1.09) becomes 10.62. Cell b3 (10.62) remains as is. Then cell b4 becomes 13.26 and b5 remains as is.

Spreadsheet should after look like

SKU	price
H100L200-245-PK10	10.62
H100L200-245-PK100	10.62
H100L245-290-PK10	13.26
H100L245-290-PK100	13.26

<tbody>
</tbody>

I have 4518 rows(including header rows). So, cannot do it manually.

What I attempted:

In cell B2 I typed:

=INDEX($B$2:$B$4518,ROWS(B$2:B2)+1)

Then selected that cell along with b3 and dragged down. Works at treat if I drag down down to only b4 then reselect all and drag down again. But if I select and drag down further it doesn't work.

The pattern is being picked up in that formulae is only in every other row. But it is changing the skipped rows value so that for example if I drag four down, the price column is:

price
10.62
10.62
13.26
13.26
11.62
11.62
14.26
14.26

<tbody>
</tbody>