-1

I have a list of 44 names i need them to fill down a column in multiples of 55

each one to be multiplied 55 times

I'm using libre office

Does that make sense if it doesn't please let me know.

1 Answer 1

3

This should be a good starting point:


First, you need a formula that would repeat a number N times before changing to the next number (To use in our indexes later). This is where QUOTIENT comes to use: it returns whole part of division operation.

So we divide current row index by N, 3 in my example:

QUOTIENT(ROW(B1),3))

Which returns 0,0,1,1,1,2,2,2...

(ROW(B1) returns index of the cell B1, which is 1)

Now this is not right, as we need the first element to be repeated 3 times, not 2. That's why we need to shift it by one:

QUOTIENT(ROW(B1)-1,3))

Which returns 0,0,0,1,1,1,2,2,2...

Now we need to convert these indexes to cell values, and that's where OFFSET() comes to play. It takes

1) starting point cell
2) vertical offset
3) horisontal offset

If we pass it our first value cell, A1 (with absolute addressing using $ which prevents it from changing when you copy this formula), and put result of the previous formula as vertical offset, and 0 as the horizontal offset, this will give the result from the screenshot.

2
  • Thanks very much however it would really help me if you could explain the formula as i dont have time
    – James
    Jun 29, 2016 at 14:36
  • Put your 44 names in the column A, change "3" in the formula to "55". Jun 29, 2016 at 14:48

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.