Formula

[antsrik]

Hi


I have a excel worksheet with monthly data Jan-Mar 2009 & 2010, I want to compare similar months in two years 2009 and 2010.
For instance if I have all 3 months in both years I would add Jan-Mar 2009 and compare this total to the total for Jan-Mar 2010

My problem is that there may be missing data for a month and I will impute a figure for the missing month in the total column. For instance for C1 where march 2010 is missing, the total for 2010 would be (jan 2009 + feb 2009)/( (jan 2010 + feb 2010)*total 2009
Another example is C3 where Feb 2009 is missing, the total for 2009 would by (jan 2009 + mar 2009)/(jan 2010 + mar 2010)*total 2010
This excel sheet is large and there are a few more scenarios that are possible, I would like to create a master formula that would take the above scenarios plus the others that are possible into consideration. I want to be able to copy this formula and it will calculate based on the circumstance for each row.

2010 2010 2010 2010 2010 2010 2009 2010
JAN FEB MAR JAN FEB MAR TOTAL TOTAL
C1 28,972,676 29,640,936 28,087,224 29,092,874 28,642,103 26,357,469 86,700,836 84,092,446
C2 23,850,643 22,315,117 26,179,365 24,433,839 26,593,571 72,345,125 65,452,423
C3 848,248 809,443 6,805,455 7,048,795 7,124,568 2,496,507 20,978,818

 

[Gerald Higgins]

Could you have a situation like these ?

a) Data for Jan 2009 is missing, and ALSO data for Jan 2010 is missing
b) Data for Jan 2009 is missing, and ALSO data for Feb 2010 is missing

If yes, it's going to be really difficult to interpolate the missing numbers.

 

[antsrik]

two similar months must have data between the years

 

[saturdaygig]

My problem is that there may be missing data for a month and I will impute a figure for the missing month in the total column. For instance for C1 where march 2010 is missing, the total for 2010 would be (jan 2009 + feb 2009)/( (jan 2010 + feb 2010)*total 2009
QUOTE]

I don't think that will work, because if any 2009 data is missing its total will be erroneously low. Although maybe if you complete the earliest year first and go forwards only, so that your previous year total is always complete. As Gerald says, this could be very complex because there are so many possible scenerios.
Why not produce a full data set manually first as a baseline, using the best rule that fits each situation. Then is you save this as a baseline (presumably your historic data won't change) you only need to find a formula for each new month that comes up missing as it occurs.

 

[Gerald Higgins]

two similar months must have data between the years

So I think that means you won't have a situation like I mentioned as scenario (a).

What about (b) ?

 

[antsrik]

b is out, you if you have jan and march in 2009 you must have at least jan and march in 2010 or else the data will not be used

 

[Gerald Higgins]

I think saturdaygig's suggestion is a good one.
I.e. build a baseline, as well as you can.