Hi,
I going crazy trying to come up with a formula for the following table that will look at the cumulative shortfall for prior months and if available add the shortfall to the revenue in the current month. For example the cumulative shortfall in Aug is 41 (18 in July, 17 in June,& 6 in May) and there is 11 extra units that can be sold (August amount in Stock-Sales column). I would like the formula to add the 11 units to sales in august to arrive at 20 units. Next month, there should only be 30 units available in the shortfall as 11 were used in August. In September the formula needs to know that there are only 30 units of shortfall remaining this month (11 of the 41 were used in August) and as result only 30 can be added to revenue in September even though there is stock of 36 in the the Stock-Sales Column. Obviously, the remainder of the year has zero shortfall remaining so if the formula is correct the revenue should stay the same as is for Oct-Dec.
Thanks,
I going crazy trying to come up with a formula for the following table that will look at the cumulative shortfall for prior months and if available add the shortfall to the revenue in the current month. For example the cumulative shortfall in Aug is 41 (18 in July, 17 in June,& 6 in May) and there is 11 extra units that can be sold (August amount in Stock-Sales column). I would like the formula to add the 11 units to sales in august to arrive at 20 units. Next month, there should only be 30 units available in the shortfall as 11 were used in August. In September the formula needs to know that there are only 30 units of shortfall remaining this month (11 of the 41 were used in August) and as result only 30 can be added to revenue in September even though there is stock of 36 in the the Stock-Sales Column. Obviously, the remainder of the year has zero shortfall remaining so if the formula is correct the revenue should stay the same as is for Oct-Dec.
Thanks,
