Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
specify which month's columns to use in mtd calculation
I have 12 columns for example 2007 (Jan-Dec) and then 12 more columns for
2008 (Jan-Dec) all lined up horizontally. At the end of the 12 monthly 2008 columns I have a MTD Diff column. I need a formula to check on the date or month I specify and ony calculate up to the matching month. Using 2007 and 2008 as examples, the 2007 data is all filled in all the way to Dec but I only want to add up Jan and Feb or whatever the matching amount of months I am currently up to. Below are a couple of examples. The 1st example shows Feb in G1 and a 2 in G4. This shows that I want it to look at Feb and only give me a MTD Diff for 2 months (Jan and Feb). Then as the second example shows, if I enter Mar in G1 I want it to calculate with three months of columns and return a MTD Diff of 3 in G4. The total for Jan and Feb 2007 is 4. The Total for Jan and Feb 2008 is 6, so the diff is 2. In the second example the total for Jan-Feb-Mar 2007 is 6 and the total for Jan-Feb-Mar 2008 is 9, so the diff is 3. Currently I have to edit the formula's in the MTD Diff column to force it to only use the months I want. I would like to automate this so I don't have to change it each month manually. A B C D E F G 1 Feb 2 2007 2007 2007 2008 2008 2008 MTD 3 Jan Feb Mar Jan Feb Mar DIFF 4 2 2 2 3 3 3 2 A B C D E F G 1 Mar 2 2007 2007 2007 2008 2008 2008 MTD 3 Jan Feb Mar Jan Feb Mar DIFF 4 2 2 2 3 3 3 3 Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for cells containing last month's date | Excel Discussion (Misc queries) | |||
divide a month's target into weeks | Excel Discussion (Misc queries) | |||
Finding this month's cell | Excel Worksheet Functions | |||
PivotTable contains last month's dates | Excel Discussion (Misc queries) | |||
Finding the most recent month's (or whatever) data | Excel Discussion (Misc queries) |