Find average value based on dates

Question

I need to find average value which is related to dates. I have below things and need to find average running value on 2015/05/01 which is 50, so how do i program it?

double value = 0;
DateTime firstdate = Convert.ToDateTime("2015/02/01");
value = 20;

DateTime lastdate = Convert.ToDateTime("2015/06/01");
value = 60;

Basically you want to find average values of double variable not date. Right? — Microsoft DN, Jul 07 '15 at 08:40
What calculation needs to be done? What is the desired outcome? I couldn't quite follow the sample. — rdoubleui, Jul 07 '15 at 08:40
i need to find the value of double variable, based on previous date and next date. so for `2015/05/01` this date my desired outcome is `50` — Purvesh Desai, Jul 07 '15 at 08:45
How are you calculating 50? Your question is not clear at all. You cant get solution if your question is unclear. — Microsoft DN, Jul 07 '15 at 08:47
So you are saying on 2015/01/01 value is 10, on 2015/02/01 -- 20,2015/03/01 --30 and 2015/06/01 -- 60... that is why on 2015/05/01 it is 50 ????? — Sagar Shirke, Jul 07 '15 at 08:50
ok. `2015/02/01 = 20` `2015/06/01 = 60` so find average `2015/04/01 = 40` now i need to find the value for `2015/05/01` which is average of `2015/04/01` and `2015/06/01`. Therefore i get the average result which is `50` on `2015/05/01` — Purvesh Desai, Jul 07 '15 at 08:51
What is the `double` for then, if you're looking for an average date? — rdoubleui, Jul 07 '15 at 08:52
@rdoubleui: i have a date for to find average value, but the question is how to find. — Purvesh Desai, Jul 07 '15 at 08:52
If you know `2015/04/01 = 40` and `2015/06/01 = 60` then why don't you just take average of 40 and 60?? whats the meaning of using dates here? — Microsoft DN, Jul 07 '15 at 08:57
@Microsoft DN: I will have 2 dates with values which is start date and end date, which may be anything. And between those 2 date there is another date which may be nearer to start date or nearer to end date, so based on date differences i need to find the value for that date. — Purvesh Desai, Jul 07 '15 at 09:00
We are talking about linear interpolation here. I added a solution below, and discussed about why you don't get exactly `50` (but how you could get the exact value under the right assumptions). — Xavier Peña, Jul 07 '15 at 09:38

score 2 · Accepted Answer · edited May 23 '17 at 11:51

So basically you want a linear interpolation between dates.

Use this:

static public double LinearInterpolation(double newOaDate, double firstOaDate, double lastOaDate, double firstValue, double lastValue)
{
    if ((lastOaDate - firstOaDate) == 0)
    {
        return (firstValue + lastValue) / 2;
    }
    else
    {
        return firstValue + (newOaDate - firstOaDate) * (lastValue - firstValue) / (lastOaDate - firstOaDate);
    }
}

Usage:

DateTime firstDate = Convert.ToDateTime("2015/02/01");
double firstValue = 20;

DateTime lastDate = Convert.ToDateTime("2015/06/01");
double lastValue = 60;

DateTime newDate = Convert.ToDateTime("2015/05/01");
double newValue = LinearInterpolation(newDate.ToOADate(), firstDate.ToOADate(), lastDate.ToOADate(), firstValue, lastValue);

// RESULT: newValue = 49.666666666666671

So why is it that newValue = 49.666666666666671? Because not all months have 30 days, so not every 1st of the month is equidistant.

If you want to get the exact 50 then you will be forced to use just the month value, and use the linear interpolation in a smart way (myDate.Month or the like). Link to the linear interpolation: C# Linear Interpolation

Natechawin · Answer 2 · 2015-07-07T09:45:53.163

With limited information so I choose to use Linear Formula

Function :

private static double Calculate(KeyValuePair<DateTime, double> startDate, KeyValuePair<DateTime, double> endDate, DateTime targetDate)
    {
        // It's X.
        var days = (endDate.Key - startDate.Key).TotalDays;
        // It's Y.
        var value = (endDate.Value - startDate.Value);

        // You get the value of slope here.
        var slope = value / days;

        // Suppose x == 0 (change linear starting point to (0,0) in other word change starting date to date 0).
        var constant = startDate.Value;

        var daysToFind = (targetDate - startDate.Key).TotalDays;

        return (slope * daysToFind) + constant;
    }

Usage :

        CultureInfo culture = new CultureInfo("en-US");

        var firstDate = new KeyValuePair<DateTime, double>(Convert.ToDateTime("2015/02/01", culture), 20);
        var lastDate = new KeyValuePair<DateTime, double>(Convert.ToDateTime("2015/06/01", culture), 60);
        var targetDate = Convert.ToDateTime("2015/05/01", culture);

        var result = Calculate(firstDate, lastDate, targetDate);

With this you can predict any value in any days you want (by very uncertainty linear formula).

Like the above answer. The result value is 49.666666666666671 because the numbers of days in a month is not exactly 30.

Find average value based on dates

2 Answers2