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.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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:
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:
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.