If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Interpolation?
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-seriesTrend 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 |
#2
|
|||
|
|||
Interpolation?
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" wrote in message ... 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-seriesTrend 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 |
Thread Tools | |
Display Modes | |
|
|