 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Time series calculation
I have a quantity that must be multiplied by a cumulative % factor at each period over a time series. At each period of this time series I add another quantity that must then be multiplied by the same series of factors starting from the beginning. The resulting list of factored quantities in each time series are totaled. The three components here, the time series, the list of quantities and the list of factors are quite big so I am looking for a formula, an array formula or otherwise that will apply these calculations to these values without having to make a huge array of discrete calculations. The logic/structure of the data looks like the array below. Time Period 1 2 3 Factor 90% 93% 95% Starting Qty1 1000 900 837 795.15 Factor 90% 93% Starting Qty2 1300 1170 1088.1 Factor 90% Starting Qty3 1100 990 Total 900 2007 2873.25 I have been trying to find a way to do this with an array formula but I can't find the way to stagger the new quantities being started up in each suceeding time period. Anyone got any good ideas about this? Thanks in advance, RD Wirr Not sure where the last row of data comes from. To add every second row in column B: =SUMPRODUCT(--(MOD(ROW(B1:B6),2)=0),B1:B6) Please clarify best wishes  SignatureBernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email >I have a quantity that must be multiplied by a cumulative % factor at each > period over a time series. At each period of this time series I add [quoted text clipped - 24 lines] > Thanks in advance, > RD Wirr Hi Bernard, The data I showed here was just the logical structure rather that the actual way I had my data in the spreadsheet. So there was no interim rows of the factors. I was already multiplying the factors in each data row offsetting the factors for each successive Qty and time period. I was just looking for a more elegant way to work the offset rather than using a simple manually offset formula in each cell. But thanks anyway. Rgds, RDW > Not sure where the last row of data comes from. > [quoted text clipped - 30 lines] > > Thanks in advance, > > RD Wirr >I have a quantity that must be multiplied by a cumulative % factor at each >period over a time series. At each period of this time series I add another [quoted text clipped - 20 lines] >Thanks in advance, >RD Wirr Here's another way of setting up your data that might work out better. In the formulas, I have assumed that your data is in rows 11-30, with time periods in C10... Somewhere on the worksheet, you have a column of factors attributable to each time period. Name this range "Factors". It might look like: 90% 93% 95% 98% etc. Here is your data table (note that the time period values are calculated per the formula below: Qty 1 2 3 4 5 Starting Qty1 1000 900 837 795.15 Starting Qty2 1300 1170 1088.1 Starting Qty3 1100 990 C11: =IF(AND(C$10<=COUNTA($A$11:$A$30),COUNTA($A$11:$A11)<=C$10), PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"") Fill the formula to the right for as many columns as there are time periods, then fill down for as many Starting Qty's as there are, or may be. In the formula, adjust the $A$30 parameter to reflect the maximum number of Starting Qty's that might exist. e.g. you could change it to $A$1000 =IF(AND(C$10<=COUNTA($A$11:$A$1000),COUNTA($A$11:$A11)<=C$10), PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"") To SUM each time period, in, for example, C9: C9: =SUM(C11:C1000) In the formula in the Data Table, note that the COUNTA function adjusts where data appears within the table, so the cells in column A should be blank. If the cells have a formula in them, we will need a different test than COUNTA on that column. But that's a simple change. --ron Hi Ron, Thanks for the formula. That works perfectly. Clever stuff. Regards, RDW > >I have a quantity that must be multiplied by a cumulative % factor at each > >period over a time series. At each period of this time series I add another [quoted text clipped - 64 lines] > that column. But that's a simple change. > --ron >Hi Ron, > >Thanks for the formula. That works perfectly. Clever stuff. > >Regards, >RDW You're welcome. Glad to help. Thanks for the feedback. --ron If you can arrange your source data as shown, only one formula is needed: http://www.freefilehosting.net/download/3bejf Hi Herbert, Very nice solution. I will probably use Ron's suggestion in this case because it's easier for me to arrange my data with his solution, but the Pivot table way is good and it gives me an idea for fixing another application I have. Thanks much, RDW > If you can arrange your source data > as shown, only one formula is needed: > http://www.freefilehosting.net/download/3bejf |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.