0
Former Member
Dec 08, 2009 at 01:10 PM

# Calculate Linear Change (CR XI)

29 Views

Hi All,

I have a real challange and I'm Struggling...

I need to calculate the number of days within a range over a number of 'Visit Dates'. The calulation needs work out the linear change over time (in days). Here is the example to best explain what I'm talking about:-

Example Table

Visit Date Value Range Low Range High

04/08/2009 1.7 2 3

18/08/2009 2.3 2 3

25/08/2009 2.6 2 3

29/09/2009 2.2 2 3

We have values from 4/8 to 29/9 ie 56 days

From 18/08 to 29/09 the values are all within the Low/High range so 42 days definitely in range

From 4/08 to 18/08 the value climbs from 1.7 to 2.3 in 14 days

So the rate in increase of the value is (assuming linearity)

is 0.6/14=0.0428571 units per day

So the value will hit 2.0 in 0.3/0.0428571 = 7 days

During these 7 days the value is between 0.7 and 2.0, therefore the value is below the target range (between 2 and 3), and for the next 7 days the value will be between 2.0 and 2.3 and is therefore in range

So overall, of the 56 days in question, 7 are below range and 49 are in range. This adds up to 87.5% of days in range.

I hope that makes sense and if anyone crack how to do it I will eternally grateful.