Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in a number x, y times in y cells
This is a difficult one to explain, so I will provide an example:
a 1 b 2 c 3 d 10 e f g h i j 2 k 5 Rows a through c contain a value for a given period, while row d contains a lump sum for all future periods. Row j is the average of rows a through c (which I am using as an estimate of future periodic values), while row k is row d divided by the average calculated in row j (the number of periods necessary until the lump sum is reached). What I am trying to do is then repeat the average calculated in row j in rows e through i the number of times shown in row k (thereby giving me 8 periodic values as opposed to 3 periodic values and one lump sum). Any ideas on how to do this? Thanks so much for the help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in a number x, y times in y cells
Not sure, but perhaps this plunge
might start the ball rolling here <g .. Assuming the example data is in A1:B11, where you probably have in B10: =AVERAGE(B1:B3) in B11: =B4/B10 (I'm just reading it straight from your post) Then, perhaps just put in B5: =$B$10, and copy B5 down to B9 Is that it ? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Damien" wrote in message ... This is a difficult one to explain, so I will provide an example: a 1 b 2 c 3 d 10 e f g h i j 2 k 5 Rows a through c contain a value for a given period, while row d contains a lump sum for all future periods. Row j is the average of rows a through c (which I am using as an estimate of future periodic values), while row k is row d divided by the average calculated in row j (the number of periods necessary until the lump sum is reached). What I am trying to do is then repeat the average calculated in row j in rows e through i the number of times shown in row k (thereby giving me 8 periodic values as opposed to 3 periodic values and one lump sum). Any ideas on how to do this? Thanks so much for the help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in a number x, y times in y cells
That would do it in this case, but my problem is that I'm trying to automate
the process. I'm using a Bloomberg feed that would automatically fill in data for five years and then one lump sum for all future years. Those values will always be different, so I will always have a different average and number of times that the average divides into the lump sum... I suspect that there isn't a simple function to do this - perhaps I'll play around with VBA and see if I can work something out. Thanks for the response though. Take it easy. "Max" wrote: Not sure, but perhaps this plunge might start the ball rolling here <g .. Assuming the example data is in A1:B11, where you probably have in B10: =AVERAGE(B1:B3) in B11: =B4/B10 (I'm just reading it straight from your post) Then, perhaps just put in B5: =$B$10, and copy B5 down to B9 Is that it ? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Damien" wrote in message ... This is a difficult one to explain, so I will provide an example: a 1 b 2 c 3 d 10 e f g h i j 2 k 5 Rows a through c contain a value for a given period, while row d contains a lump sum for all future periods. Row j is the average of rows a through c (which I am using as an estimate of future periodic values), while row k is row d divided by the average calculated in row j (the number of periods necessary until the lump sum is reached). What I am trying to do is then repeat the average calculated in row j in rows e through i the number of times shown in row k (thereby giving me 8 periodic values as opposed to 3 periodic values and one lump sum). Any ideas on how to do this? Thanks so much for the help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in a number x, y times in y cells
Thanks for the clarification !
.. That would do it in this case, but my problem is that I'm trying to automate the process. Yes, of course, that would be the usual inference <g Just to move it along a little further here .. ".. Bloomberg feed ..." This may well be second nature to you, but probably not to a lot of us Perhaps you could either* paste some representative data of how the feed actually appears in the sheet in plain text in your reply (showing some variations). Tell us how we can "identify" which cell in the col will contain the lump sum, how to identify when one feed ends and the other begins, etc, etc (the variation patterns) *Alternatively, upload a small sample file and paste the *link* to it in your reply Some free filehosts that could be used to upload your sample: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php For cjoint.com (it's in French), just click "Browse" button, navigate to your folder select the sample file Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. Kindly note that no attachments should be posted *directly* to the newsgroup I'm not saying I'd ultimately have a further (formula) suggestion to offer (it may be beyond me), but, if you deepen your clarification further here as suggested above, it'll be visible to the many other regular responders out there, some of whom may well have something to offer you. Either way, kindly post a closure response here to let us know whether you're keen to pursue this further. Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Damien" wrote in message ... That would do it in this case, but my problem is that I'm trying to automate the process. I'm using a Bloomberg feed that would automatically fill in data for five years and then one lump sum for all future years. Those values will always be different, so I will always have a different average and number of times that the average divides into the lump sum... I suspect that there isn't a simple function to do this - perhaps I'll play around with VBA and see if I can work something out. Thanks for the response though. Take it easy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Filling cells (autofil?) in Excel 2003 | Excel Discussion (Misc queries) | |||
How to link cells and keep number format altogether | Excel Discussion (Misc queries) | |||
Return number of cells filled | New Users to Excel | |||
Number of times a number is shown | Excel Worksheet Functions |