Prev: Linking to external files
Next: Formula Help
From: Mel on 28 May 2010 11:54
What fx to use for the following scenario:
Month-End Dates (for many years) in column A
A dollar Amount for each Quarter End in Column B (correlating to the
month-end date).

We would like a function to easily copy down column B that will interpolate
values in between the dollar amounts for each quarter end.

NOTE: The edit-fill-series>Trend works for a manual fix however we have
thousands and thousands of dates with quarter end dates so a faster approach
would be much more efficient.

Thanks for any guidance/expertise you may provide.

Mel
From: Steve Dunn on 1 Jun 2010 11:09
Assuming your quarter end months are 3, 6, 9, and 12:

in B4:

=VLOOKUP(EOMONTH($A4,-MOD(MONTH($A4),3)),
$A$1:$B$5000,2,0)+(VLOOKUP(EOMONTH($A4,
3-MOD(MONTH($A4),3)),$A$1:$B$5000,2,0)-
VLOOKUP(EOMONTH($A4,-MOD(MONTH($A4),3)),
$A$1:$B$5000,2,0))*MOD(MONTH($A4),3)/3

copied in between all quarter ends.

HTH
Steve D.


"Mel" <Mel(a)discussions.microsoft.com> wrote in message
news:7B148BE3-97C6-4E88-A142-3FDB25906745(a)microsoft.com...
> What fx to use for the following scenario:
> Month-End Dates (for many years) in column A
> A dollar Amount for each Quarter End in Column B (correlating to the
> month-end date).
>
> We would like a function to easily copy down column B that will
> interpolate
> values in between the dollar amounts for each quarter end.
>
> NOTE: The edit-fill-series>Trend works for a manual fix however we have
> thousands and thousands of dates with quarter end dates so a faster
> approach
> would be much more efficient.
>
> Thanks for any guidance/expertise you may provide.
>
> Mel

 | 
Pages: 1
Prev: Linking to external files
Next: Formula Help