Distributing numbers across rows below them

[SwayamC]

Hi,

I have TABLE A with incoming dates of cargo trains on 2 platforms (Platform 1 and Platform 2). In Table A, a value in Plaform 1 or 2 columns is number of days spent by train on the platform. I need to distribute the number of days a train will spend on the platform across the days the platform was occupied by the train.

For e.g., if a train comes to Platform 2 on 09-06-2018 and it stays there for 1.23 days, the final table should have 1.00 under Platform 2 on 09-06-2018 and 0.23 under Platform 2 on 10-06-2018.

In case Platform 2 has an incoming train on 10-06-2018 with stay time as 1.33 days, it would get the remaining day = 1.00-0.23 = 0.77 on 10-06-2018 and the remaining period of stay = 1.33-0.77 = 0.56 should be shown for 11-06-2018.

I have manually expanded it for the data given in TABLE A and the desired result is shown in TABLE B.

I need to do this for an entire year of data.

Kindly help. Thank you!

[AliGW]

Welcome to the forum.

Can I check that you are really using the OLD Excel 2007???

[SwayamC]

Thank you

Yes, my workplace provides me Excel 2007 only.

[JeteMc]

Try pasting the following into cell F3, then drag the fill handle down to cell F16 and then, while F3:F16 are still selected, drag the fill handle over to cell G16:

Please Login or Register  to view this content.

Let us know if you have any questions.

[SwayamC]

This worked perfectly. Thanks a lot for sharing the solution.

If it's not too much to ask, could you please share your thought process to arrive at this solution?

[JeteMc]

The 1 because 100% is the largest portion of a day that a train can spend on a platform.
The SUM(B$3:B3) to get a cumulative amount of time spent on a platform by the trains.
The SUM(F$2:F2) to get the cumulative time already distributed in table B.
MIN because we want the lesser of 1 and the difference between the results of the two SUM functions.
I hope that makes sense.
You may get a better idea of how the formula works by selecting a cell, such as F13, and utilizing the Evaluate Formula feature found on the Formulas tab.
Let us know if you have any questions.

[SwayamC]

Great, it helped me understand how to approach such problems. Thanks a lot.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.