PDA

View Full Version : Formula to distribute a number in forward years with the cond. it is equals an amount



cbs81
04-17-2008, 02:10 AM
Hey all

I have some serious difficulties with this formula
Say this is the month of July 06, and Aug 06 onwards are forecasts as below: If there is a number in July stage 1 > 0 the formula should enable a projection like the following:

It will distribute what is in column B which is 5, in each month, and the left over will be distributed in the last month until it equals 18 ( which is the last column)

All forecasts should not exceed 5 for any one month.
In Stage 2, the formula should distribute the left over from 5 and what was distributed in stage 1. In this instance 2 should be automatically distributed. The following months should distribute 5 until the last column of 4. then carry this principal to the rest of the spreadsheet.





NOW….. Say the user enters the actuals for Jul 06 and there happen to be 7 sales for Stage 1 and 3 sales for stage 2 for this particular month. The formula would then output a change from the Above distribution to the following given that From Aug 06 are still projections?:





There are still 0’s between Jul06 and Oct 06 and that the distribution of 2 for stage 2 for oct 06 remains unchanges..
Please create a formula that will allow manual entries as months pass in the stages and then will recalculate the forecasted fields using the logic above.. Remembering each of the forecasted fields must have a total of 5 for each Column distributed whether it be in one row, as in Sep 06 or over two rows as in oct06 (3 & 2) spread out over stage 1 and two as in the diagram directly above.
Please ensure that in each row the distribution of x lots/stage is not breached. It must total this at all times even when actual figures are entered.. it should just simply reforecast everything.
If you can do this… I will be extremely greatful and i will stop pulling my hair out... lol .. im getting bald..
Please solve it in whatever way you can.. if you need to create another column outside this field to keep some data.. please do it in whatever way you can…

If you cant do it with a simple formula, my second preference would be VBA with an ability for the code to select which month to project from, if it cant distinguish between a manually entered field and a formula/projection field..

pleeeease help somebody

if you can please also email me the solution to:

enquiries@chandrabusiness-solutions.com (enquiries@chandrabusiness-solutions.com)

preferably in a workbook if you can..



Thanking you with every bit of me
Edited by Simon Lloyd for post length, workbook supplied below!

cbs81
04-17-2008, 02:31 AM
please view the attached for the problem..