1

I have a excel spreadsheet like:

Current spreadsheet

What I would like:

What I'd like the spreadsheet to be like

Thus, what I am trying to achieve is for the current cell to take the value of the cell directly below value and repeat, skipping each of the directly below cells.

For example, cell B2 will take the value 10.62 (i.e. cell B3); then cell B3 will be skipped; then, cell B4 will take value of 13.26 (i.e. cell B5), and following suite...

I have tried this formula:

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

Then, selecting that row and the row below, which doesn't have any formulae, and dragging down. But, sadly unless I only drag down one then reselect it doesn't work correctly. It seems to take the first selected cells value and then adds 1.

  • please post also screenshot of current- and desired outputs, your question is unclear now. – Máté Juhász Oct 3 '16 at 11:52
  • I have updated the question – Vaishal Patel Oct 3 '16 at 12:13
0

So, if I'm understanding correctly, in row 2 you want the value from row 3, in row 3 you want to keep its own value, in row 4 you want the value from row 5...etc?

If that's correct, there's a fairly straightforward process you can use - in C2, enter the formula =$B3. In C3, enter the same formula, =$B3. That should put the same value into both cells. Then highlight both cells and use the fill handle to copy them into the other cells. This should get you the repeating pattern you want.

  • I need the values in the B column though. – Vaishal Patel Oct 3 '16 at 13:57
  • You would use the C column to create the values, then you can use Copy | Paste Special (Values) to place the results over the top of the existing values in B. – Werrf Oct 3 '16 at 14:00
0

because you will need the B value for 2 cells, copy the B column to the C column otherwise will eventually get a reference issue

then in the B column, replace with the following formula

  • =IF(EVEN(ROW()),C3,C2)

The if checks to see if needs to go down 1 or just go across.

Row() returns the row this is on. In the case of B2 it is 2 Even() checks if something is even. For a 2 this is true.

IF (test,True_Action,False_Action) . The if works by making a test then choosing either a true or a false action. Since Even is true for b2, the true_action points to C3.

Note. This has the side effect of expanding your data range. A copy /paste special could be used over the b row to replace with the new values. Then afterwards, the C column could be deleted.

  • Very insightful, I went with the option below which seems to have done the trick fantastically. – Vaishal Patel Oct 4 '16 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.