alok
12/18/2006 10:27:00 PM
Since the formula extrapolates you must have another set of x and y values so
that the value which you are extrapolating lies between one of the ranges.
Hence if you put 1000 and corresponding y value in A5 and B5 respectively,
and change the formula accordingly, you will be able to extraploate for any
value up to 1000. If the value does not change beyond 182 then your values
for A5 and B5 could be 1000 and 5.5220 respectively.
Alok
"amirstal" wrote:
> It works fine except for the last value for which it returns #DIV/0!
>
> So in my example, if I enter 182 I get in error...
>
> Thanks,
>
> Amir
>
>
> Alok wrote:
> > Hi
> > I am giving you a non-VBA answer and see if it works for you.
> > You can use the formula
> >
> > =FORECAST(A6,OFFSET(B1:B2,MATCH(A6,A1:A4,1)-1,0),OFFSET(A1:A2,MATCH(A6,A1:A4,1)-1,0))
> >
> > This assumes that your x values are in A1 to A4 in ascending order. The
> > corresponding y values are in B1 to B4 and the x value for which you want to
> > find the corresponding y value is in A6.
> >
> > Alok
> >
> > "amirstal" wrote:
> >
> > > I have the following problem with my excel table. Maybe you can help.
> > >
> > > This is the table I have:
> > >
> > > A B
> > >
> > > 33 5.5462
> > > 62 5.5556
> > > 90 5.5524
> > > 182 5.5220
> > >
> > > column A represent a number of days and B interest rate values.
> > >
> > > I'm looking for a formula that will return the interest rate of any
> > > given day that is in between 33 and 182.
> > > So if the number of days is 33, it will return 5.5462.
> > > But if it is 40, the formula will find the interest rate using
> > > interpolation
> > > from the closet values (the formula knows that 40 is between 33 and 62
> > > therefore it will use 33, 62, 5.5462 and 5.5556). The same holds for 75
> > > for example (62, 90, 5.5556 and 5.5524)
> > > I know how to make the interpolation work I just need the formula for
> > > the reference.
> > >
> > > Thanks.
> > >
> > >
>
>