Formula Help Needed

[Don]

Would appreciate help in determining the formula to do the
following:
I have created a simple worksheet to do an inventory
projection, where the ending inventory for a given week
(say week 10) is determined by the ending inventory from
the previous week (week 9), minus shipments of that week
(week 10), plus production of that week week 10).
The shipment and production data for each week are located
in other columns on the same worksheet. For both, there is
a separate column for actual history and another for
future estimates.
I need a formula to first check a week to see if it is
ealier or later than the current week and based on that
criteria then chose that week's shipment and production
data from either the actual column or the estimate column.
Cell locations are:
Week # being checked = cell B24
Current week # = cell M1
Estimated shipments = cell Q24
Actual shipments = cell S24

Thanks in advance, Don

 

[Anon]

Would appreciate help in determining the formula to do the
following:
I have created a simple worksheet to do an inventory
projection, where the ending inventory for a given week
(say week 10) is determined by the ending inventory from
the previous week (week 9), minus shipments of that week
(week 10), plus production of that week week 10).
The shipment and production data for each week are located
in other columns on the same worksheet. For both, there is
a separate column for actual history and another for
future estimates.
I need a formula to first check a week to see if it is
ealier or later than the current week and based on that
criteria then chose that week's shipment and production
data from either the actual column or the estimate column.
Cell locations are:
Week # being checked = cell B24
Current week # = cell M1
Estimated shipments = cell Q24
Actual shipments = cell S24

Thanks in advance, Don

If I understand correctly, the formula to give shipments would be
=IF(B24<M1,S24,Q24)

You might prefer to use
=IF($B$24<$M$1,S24,Q24)
Then, if you copy this and paste it (say) in the cell immediately below, you
would get
=IF($B$24<$M$1,S25,Q25)
which might help in making the corresponding formula for production if
estimated and actual production figures were (say) in the cells immediately
below those for shipments.