Interpolation based on Average

**edwoody** · 03-02-2011, 12:02 PM

Hello

First time post so please bare with me.

I have two numbers as follows:

Dec-11 1,071.69

And I have an Average for Dec-12 1,087.46.

I need to interpolate in Excel the numbers such that for each month of 2012 there is linear interpolation from 1071.69 to a max of 1,087.46. HOWEVER, the average of the interpolated numbers must equal 1,087.46.

Any ideas?
Thanks
Ed

**shg** · 03-02-2011, 02:16 PM

If there are numbers that fall in the range from A to B, then the average of those number cannot be A (or B) unless they are all equal to A (or B).

**edwoody** · 03-02-2011, 03:15 PM

Sorry it might help better if I give an example.

Dec.11 1071.69
Jan.12
Feb.12
Mar.12
Apr.12
May.12
Jun.12
Jul.12
Aug.12
Sep.12
Oct.12
Nov.12
Dec.12 Average for 2012 is 1,087.46

I need to fill the months of 2012 so that they interpolate in a linear form but in doing so the average for 2012 for all the numbers equals 1,087.46.

Thanks

**shg** · 03-02-2011, 04:05 PM

Ah.

       ---A--- ---------------------B----------------------
   1   1071.69 A1: Input                                   
   2   1087.46 A2: Input                                   
   3                                                       
   4   1074.12 A4 and down: =A$1+(A$2-A$1)*ROWS(A$4:A4)/6.5
   5   1076.54                                             
   6   1078.97                                             
   7   1081.39                                             
   8   1083.82                                             
   9   1086.25                                             
  10   1088.67                                             
  11   1091.10                                             
  12   1093.53                                             
  13   1095.95                                             
  14   1098.38                                             
  15   1100.80

**edwoody** · 03-03-2011, 02:06 AM

Thanks, but the max number cant be over 1087.46.

**shg** · 03-03-2011, 08:23 AM

I thought we already went down this path.

If you have a bunch of numbers, some less than X but none greater than X, their average MUST BE LESS THAN X.