Forum Discussion

shivraj mohite's avatar
shivraj mohite
Copper Contributor
May 27, 2017
Solved

Create a excel which changes dynamically with two variables described below.

I have data with me as elaborated below. And I want to calculate no. of passengers in the system at a particular time slot. Departure Time No. of  passengers 10% 10% 10% 10% 15...
excel
Macros and VBA
  • Yury Tokarev's avatar
    Yury Tokarev
    May 28, 2017

    Hi Shivraj,

     

    please see attached for my solution. I have created two tables. The first one contains timeslots, and the second one - the number of passengers in each timeslot. Then, I used SUMPRODUCT formula to summarise the number of passengers in the system up to the specified time, excluding those, whose flights have departed. If the departure times are in the range B12:B17, timeslots in D12:K17, the number of passengers in each timeslot in the range D21:K26, and the time entry range in the cell C29, then the formula to summarise the number of passengers in the system would be 

    =SUMPRODUCT($D$21:$K$26*($D$12:$K$17<=$C$29)*($B$12:$B$17>$C$29))

     

     

     

    Yury

     

     

shivraj mohite's avatar
shivraj mohite
Copper Contributor
May 28, 2017

Thank you for your first reply, I have one more question, how to display the no. of passengers in all 10 minute slots, eg. 13:50to 14:00, 14:00 to 14:10?

Yury Tokarev's avatar
Yury Tokarev
Iron Contributor
May 28, 2017

Hi Shivraj,

 

you can create a list of timeslots in one column (each of them showing the end of the respective 10 minutes interval), then use the same formula in the next column with a difference that in each row it would refer to the respective timeslot end time in the same row (as shown in the image below). Please find the ammended file attached.

 

 

 

Thanks

Yury

 

 

 

Resources