How to autofill column incrementally

[szegedin]

I'm sure this is a simple question that has been answered but if I knew how to phrase it I would have found it in search...

How can I autofill cells as follows:

1
x
x
x
10

Whereby it would generate
1
2.5
5
7.5
10

Fill > Series is not working for me, because my actual start/end numbers are
1000 - 1,300,000
Basically I need it to generate the average increment between rows and fill them with that increment per step.

Many many thanks if you can explain this to me.

[acknak]

Greetings and welcome to the community forum!

Sorry, there's no "fill" operation that I'm aware of that will do this. You can enter 1, then 2.5 below it, then drag those down. But that's not really practical for a large number of rows.

Here's one approach for your specific situation:

You know you need 1300 rows, incremented by 1000.

In the location box (leftmost in the formula toolbar), enter A1:A1300, then Edit > Fill > Series: Start: 1000, Increment: 1000; OK

[RusselB]

With the example you gave, you could use the formula

Code: Select all

=A1+1.5+if(A1<>1;1;0)

However your later comment makes that incorrect.
You need something more complicated and I can't write it at the moment.
Unless someone beats me to it, I will figure out the formula and post it later.

[szegedin]

acknak Thanks so much for the welcome and for the response.

Ahh, if only it were that simple.

In my case, I have 144 rows. First is 1000 and last is 1,300,000. So I could divide the difference and increment it that way and it almost works.

But I would like to have a formula that works and I am sure there is one out there -- I'm pretty sure Excel does this with its Fill function. In other words it doesn't require an "increment" -- just a start and end -- which is where the snag is.

It's just a very necessary calc. Imagine your interest rate is 5% on day one of the year and 7.5% on the last day of the year, steadily (linearly) increasing. You want to chart the interest rate on each day in between. Etc etc.

Some genius out there surely has the answer. RusselB would be much obliged if you know it.

[Lupp]

I am surely "the genius out there" but nonetheless I cannot understand the given examples consistently.

Therefore I simply ignore the original question and understand the last post of the questioner in the following sense:
Starting with a certain value A an arithmetic sequence should be generated reaching a given last value Z after a given number N of steps.
The increment of the sequece has to be d=(Z-A)/N and the sequence will have (N+1) elements.
Let us name A as the zeroth element a0. Z will then be the n'th element aN.
Every j'th element then calculates as aj = a0 + j*d with j running from 0 to N.
We see that these elements can easily be generated by a filled down formula in Calc.
If we allow for a big enough output range we may even generate the complete sequence by one array formula adapting the results automatically when the parameters A, Z and N change.

See attached.

[szegedin]

Wow, damn man, I'm impressed. Reading your answer and the attached, I sure am glad I'm not the kind of guy that has the answers to these questions, and I'm sure glad that you are. Very grateful for the solution. All hail thy worthy genius.

It's quite clunky considering that Excel (I think) does this on the fly.

http://www.extendoffice.com/documents/e ... alues.html

If I wanted to switch around variables, it could get hairy fast. For example, what If I wanted to punch in changes to the interest rate on May 12th, September 13th, and November 10th. And drag down the whole solution. As in, finding all the numbers in between. Doable - just clunky, because unless I'm mistaken, in this formula I have to recalculate the interval. This is not the problem I'm working on but it's exactly the same dynamics.

Thanks again for getting me to a solution - now on to some fun modeling.

I am surely "the genius out there" but nonetheless I cannot understand the given examples consistently.

Therefore I simply ignore the original question and understand the last post of the questioner in the following sense:
Starting with a certain value A an arithmetic sequence should be generated reaching a given last value Z after a given number N of steps.
The increment of the sequece has to be d=(Z-A)/N and the sequence will have (N+1) elements.
Let us name A as the zeroth element a0. Z will then be the n'th element aN.
Every j'th element then calculates as aj = a0 + j*d with j running from 0 to N.
We see that these elements can easily be generated by a filled down formula in Calc.
If we allow for a big enough output range we may even generate the complete sequence by one array formula adapting the results automatically when the parameters A, Z and N change.

See attached.

[RusselB]

Excel may do this on the fly, but you're having to purchase Excel, therefore are paying for someone else to do the hard work in advance.
There are times when Excel is better than OpenOffice, but, in my experience, those times are few and far between.
If part of the reason of using OpenOffice over Excel is to save the purchase price (several times), then you'll have to put up with OpenOffice's "clunkiness"... or edit the code to make it work better and submit the alterations as help for this project.

[szegedin]

I agree - I'm an Ubuntu user and a believer in open source. And great to see people using their minds to work through the math.

Actually looking into it further, I think Excel does not do the function discussed above, but rather it was some extra paid plugins that do it. So you have to pay for plugins on top of the cost of Excel. That said, doing this on the fly is a very, very handy calculation.

Considering what the free version of Tableau does, for example, I'm surprised there isn't an elegant, open source software solution for this important financial modeling operation. The search continues.

Excel may do this on the fly, but you're having to purchase Excel, therefore are paying for someone else to do the hard work in advance.
There are times when Excel is better than OpenOffice, but, in my experience, those times are few and far between.
If part of the reason of using OpenOffice over Excel is to save the purchase price (several times), then you'll have to put up with OpenOffice's "clunkiness"... or edit the code to make it work better and submit the alterations as help for this project.