Redistribute data in excel

[kay007]

Dear experts,

I need help with redistributing the differences in among my data.

The Scenario:

I have a table that is designed to pick up "actual & forecast" depending on the month selected or month with data. If there is no "actual data" then data from the forecast is picked. This is pretty straight-forward for me to implement.

However, the user add a twist that is giving me an issue about how to go about it. The twist is that the user wants to distribute the differences btw the total & FY field that spreading it across data showing forecast.

Using the attached file as a example, specifically the "Output" table (row 22)



Columns (D - K) contains the "Actual Data"
Columns (L - O) contains the "Forecast Data"

The total of columns (D - O) is computed under column P

This total column is now deducted from the FY field (column Q) and the result is now stored on column R. However, i want a situation that the data stored in column R are divided by the number of fields carrying forecasts and added to the data in the forecast cells. See attached file for the calculations.

How can now distribute the items contained in columns (S23 - S27) across all the items that are carrying "Forecasts"?

I hope i have not confused anybody

Many thanks!