Excluding Non Mature Data and Weighted Average

**helpwithexcel1** · 03-15-2010, 09:50 AM

Hello -- I am working on a monthly report that I do (real numbers have been changed!) where I want to exclude the last two months in the string and it automatically updates based on the date I put in - because this is not mature data. If you review the spreadsheet, I have the formulas started using Index functions and it is pulling in correctly (I think).

SO I have the data to grab, now I have to re-calculate the original formula using that data (see column AB highlighed in Yellow). The original data with all data, not just mature data, is in column AA... basically I need to re-create that with the new data in column AD -- but also in a weighted average formula. I have highlighted in yellow where my formulas are messed up. HELP!

Or if there is another way to do it, by all means! :-)

**ChemistB** · 03-15-2010, 04:42 PM

I'm not a financial person but it looks like your formula in AC6 is incorrect. It doesn't account for the possibility that the value in B2 may not be the same year as that in B4. Maybe something like;
=((((YEAR($B$2)-YEAR($B$4))*12+MONTH($B$2)-MONTH(B$4))-3)*2)
Does that make more sense?

**darkyam** · 03-15-2010, 05:14 PM

AC7 and AC9 can both just be AC6 or you could have the formulas that use them use AC$6 as a reference instead of them. There's also the somewhat shorter option of =MOD(MONTH($B$2)-MONTH(B$4)-3,12)*2.