Count the number of values required to sum to a total

[LAUGHINGCROW]

I posted this problem in the "functions" area and had two very helpful responses which solved the issue for a small data set. However the dataset Im dealing with is relatively large (280 columns x 8760 rows) and dragging the function into that many cells ate up all my CPU and the calculation speed was abysmal (CPU actually overheated and forced a reboot!). It was suggested that therefore a macro may be the best way to solve the problem.

The orignial post is located here:

http://www.excelforum.com/excel-form...ml#post3824265

However, I thought it may help to add a little more context to the problem and also to get a VBA solution for the entire routine and not just the portion requested in the original post (above).

Context:
The calculation can best be thought of in terms of velocity and distance. I have a data set which has a measured velocity at hourly time points for a total of 8760 hours. I also have a set number of distances which are required to be traveled (280 different distances). The units for distance are arbitrary and the distances range from 1-280. Additionally, each distance of the 280 distances is required to be travelled a different number of times (you can think of this as being that there are different numbers of "cars" travelling these distances at the velocities in the dataset). At each hourly time point the "cars" set off on their journey and looking down the data set I know the speeds they will travel at over the next hours. I need to be able to calculate the time it takes for all the "cars" to travel the required distance for each time point and to calculate the average time for each hour.

I have attached a spreadsheet of a limited data set to show what is required which should hopefully better explain the calculation. This is only a small section of the data set, as mentioned before the full data set has 8760 hours of velocity data, 280 different distances and a total of 100 "cars". Ive broken the calulcation down into 3 sections:

1. Calculate time to required distance for one car setting out at each time point
2. Calculate total aggregate time for all the cars making all their journeys
3. Divide the total aggregate time by the total number of cars to derrive the average time.
Ave time taken.xlsx
All suggestions/comments are gratefully recieved.

[lancer102rus]

Check the sound. Push the button on Sheet2 and result outputs on Sheet1.
Ave time taken.xlsm

[LAUGHINGCROW]

Well, Im astounded.

It seems to work perfectly. For the entire massive data set the total calculation time is about ten seconds. Excellent work!!!

I'll play around with it a bit to make sure its doing what I think its doing and then I'll mark the thread as solved.

I'm delighted, thank you so much!

[LAUGHINGCROW]

Well after a few days of playing with it, it seems you wrote a very robust/versatile macro!

Many, many thanks

[lancer102rus]

You are wellcome!