Repetitive division problem

[DanVG]

Hi!

I am incurring a problem related to a division that has to happen for every week. I collected a dataset of the number of ship arrivals (port calls) per country and per week. This indicator is reported on a weekly basis but I would like to obtain a value for each day of that week by dividing this weekly number uniformly over the week.

As this would be impossible to execute for 100.000 lines manually, I would like to know if there would be a way to automate this by using a formula or VBA.
You can see in the attached document that there is column country, date, port calls 1x per week, per day. Now, the purpose is to go from the 'port calls 1x per week' to the column 'per day' by making use of an efficient and effective way.

Thank you in advance!
Dan

[ChemistB]

Assuming there is at least 1 visit per week, this should work in C2 copied down

Please Login or Register  to view this content.

Round as desired.

[DanVG]

Thanks a lot! It worked perfectly

[DanVG]

Hi ChemistB,

When proceeding with this dataset, another problem occurred. I was able with your help to link the change in port calls to a particular country and date. So the following dataset is created, see attached.

Now, I wanted to insert a column which includes a lagged factor (a delay) of the difference of 15 days. So that means that the first of January now has to be linked to the difference of the 15th of January. This is easy done, when you have a list representing only one country. If you just equalise the lagged value on the first of January to the 15th of January, it works for the beginning of the period covered but not at the end since it will just start considering the values of the next country in the list.

So the question is, how to insert a lagged factor without taking the values of the next country into account?

The attached file represents the countries Aruba and Belgium and are only 2 of the 132 considered countries.

Thank you in advance.
DanVG