Progression formula

Hello everybody, I am new here and so I thank in advance anyone that might be willing to help.
I am working on a set of data where starting from an individual value (for the sake of argument 365 Euro) I need to see it developing on a daily basis in increments of 1/365th a day.
So if I input "365" in cell "A" I need the workbook to return "1" in cell "B", "2" in cell "C", "3" in cell "D", "4" in cell "E" and so on for 365 cells.
I have tried working it out on my own but with no luck, can anybody point me in the right direction as to what formula I need to use?
Thank you very much and congratulation on a very informative forum!

If you type 1 in B1 then go to Home > Fill > Series, enter a stop value of 365, click OK. That is for xl 2007 (hope similar in 2010).

Originally Posted by StephenR

If you type 1 in B1 then go to Home > Fill > Series, enter a stop value of 365, click OK. That is for xl 2007 (hope similar in 2010).

Thanks Stephen!
I have now worked out how to create the series but what I am looking for is some kind of device that automatically fills it in everytime I enter a value in the first column (A)
I am working on a spreadsheet with roughly 1000 entries and for each of these I need to see how the initial value develops in daily increments for a year (it's a yearly premium that starts earning on the first day and matures fully on the 365th day).
Any thoughts?
Cheers

You could use VBA like this - right-click the sheet tab, View Code and paste code:

Please Login or Register  to view this content.

Originally Posted by StephenR

You could use VBA like this - right-click the sheet tab, View Code and paste code:

Please Login or Register  to view this content.

Ok, I need to get myself a good book on VBA!
I've managed to do what you suggested, the result is that whatever number I enter in column 1 (A) the sub returns a series of numbers growing in steps of 1 increments up to the number that I have inputed.
I.e.: If I input 200 I get 200 cells going 1, 2, 3, 4, 5, 6, 7, 8, etc...... If I input 500 I get 500 cells and if I input 10 I get 10 cells from 1 to 10.
Ideally the result I am looking for is that if I input 365 I get 1, 2, 3, 4, 5...........365 but if I input 730 I get 2, 4, 6, 8, 10........730 and so on.
Thank you very much and please do drop me as soon as you get fed up!

You'll have to explain the logic of the increments.

Originally Posted by StephenR

You'll have to explain the logic of the increments.

I'll try,
It's an annual premium and as such, the way I need to look at it in this excercise, it is not 100% available on day 1 of the contract but it becomes available each day in 1/365ths increments.
So if the annual premium is 365 Euro, only 1 euro is available (in our jargon is "earned") on day 1 of the contract, 2 euro are available on day 2, 3 euro are available on day 3 and so on.
If the annual premium is 500 Euro, only 1.37 euro is available on day 1 of the policy, 2.74 euro are available on day 2, 4.11 euro are available on day 3 and so on.
The premium becomes fully "earned" (ie: in the second example 500 euro are available) only on day 365.
Hence the need to create a series that grows in 1/365ths increments of the initial value.
I have a set of annual premiums that are part of a portfolio of contracts and I would like to see how the premiums develop as the days go by, for instance how much premium I have available out of all my contracts on day 250 or how much premium I will have available on day 325.....
Thanks

Originally Posted by Marcol

Is this what you mean?
In B2

Please Login or Register  to view this content.

Drag Across 365 columns to Column NB, then down if required.

See the attached. (Change the values in Column A.)

N.B.
This will only work with Excel 2007 or above, prior to this Excel only has 256 columns (IV).

Thanks Marcol, the principle of this seems to be working! I will have to play around with it a bit to see if I can develop it over the various inception dates of the contracts during the calendar year as they don't all start on the same day but they all run for 365 days. The first contract incepts on the 1/1/2011 and the last incepts on the 31/12/2011 so technically between the inception of the first contract and the expiry of the last I have 730 days.
Busy evening ahead!
Cheers

Thanks again, I have managed to tweak the formula to do pretty much what I need it to do although there still is a lot that I need to do manually.
So I will give it some time to see if I can improve on it and will see how it goes.
In any event I have learned a couple of very useful things and for this reason I thank you very much!