Caclulating the sum between two known intergers assuming constant growth between them

[jonny5010]

Hi all,

This is related to my work (deadline is imminent and help woudl be greatly appreciated!). I imagine there is an easy answer but I have been unable to find it.

I have traffic counts for two years 2017 and 2032. Assuming a constant growth between these two dates, can i calculate the sum of all the values between these two years (i.e. the total number of vehciles passing during this time period). It is kind of like the x! functiion on a calculator but the intergers wont be sequential.

Daily HGVs
2017 2032

17 29

195 396

Is there a way to do this without creatng a table to caclute the number of vehicles for 2018, 2019 etc.. and then summing them?

p.s. thanks to everyone who posts on here, your advice has saved me a number of times (yes that may be a little buttering up but it is true!!)

[NeedForExcel]

Can you show an example of the values that will be summed?

[jonny5010]

Hi Deep Dave,

I included them above but i see they aren't clear.

Two sums please (there are about 80 i need to do in total!)

2014 - 17 HGV's
2032 - 29 HGV's

2014 - 195 HGV's
2032 - 396 HGV's

thanks

[NeedForExcel]

Nope! I mean to say, can you tell me all the exact values between these 2 years that are to be summed?

[jonny5010]

I want to avoid having to calculate of of the indiviual values - i do not know them and only can assume a linear growth between those two dates (it works out at approxiamtely 0.8 HGV / year increase)

I am hoping for a formula which assumes a linear growth between the two years. and then sums all of the integers automatically?

[NeedForExcel]

How did you arrive at 0.8 HGV / year increase?

I am sorry for so many questions, but I am still unclear on the requirement.

[jonny5010]

ah! sorry my mistake, wrote 2014 but meant 2017. I have values for 2014 too but didnt want to complicate things... turns out its more complicated now!

0.8 / year:

2032 - 2017 = 15
29-17 = 12


12/15 = 0.8


OK; there is a yearly increase in vehciles between 2017 and 2032. the increase in vehciles in linear.
i want to know the TOTAL number of vehicles on the road between 2017 and 2032.
I could do this by calculating a yearly increase and making a table then summing the results but that is time consuming and not effective. I would rather use a formula.
So;
the formula needs to sum all of the, at present, unknown intergers between the two years.

i think when we get the answer, i'll make a new thread to tidy up this convo and make it easier for others to follow

[MrShorty]

New Thread started here: http://www.excelforum.com/excel-gene...ween-them.html

Mathophobe warning: This post will contain calculus and geometry.

I will assume that you have had an introductory calculus course. In that course you should have learned that, among the interpretations of the definite integral are these:

1) The definite integral represents "total change".
2) The definite integral represents "area under the curve".
(If these concepts are fuzzy, then I would recommend some kind of "introduction to the definite integral" refresher. Should be several across the internet. Obviously I cannot give you a full tutorial in a single forum post)

calculus approach: given the equation for the curve (y=mx+b), which, I assume you can compute from the two points you will be given
1) Take the integral of y=mx+b
2) Evaluate at the limits of integrate and take the difference for total change (Fundamental theorem of calculus).
3) Recognize that your 1712 answer represents a "Right hand" Riemann sum, you will need to think through your limits of integration to make sure the definite integral comes to the same result (since midpoint rules would tend to be more accurate for a "straight line" like this, probably integration limits based on the 0.5 year, 2016.5 to 2032.5).

Geometric approach.
1) Since total change can be represented as area under the graph, we can use anything we know about geometry and areas to find a solution. A straight line like this forms a trapezoid with the x axis, we can use the formula for the area of a trapezoid to solve the problem.
2) Again, recognizing that we need to adapt the limits of integration to account for the "right hand sum" that is supposedly the correct answer, height of the trapezoid will likely need to be half year increments (2032.5 to 2016.5).

Can I assume that you followed that well enough to implement the algorithm? If you get stuck, where do you get stuck?