Formula to compare and sum last 4 quarters possible?

[home_toast]

Hi,

I am developing a report and I have run into some problems.

I use the formula below to sum the last 4 quarters of operating expenses:
=SUM(OFFSET(INDEX(B$4:B$13,COUNT(B$4:B$13)),-3,,4))
Data is in B8:B11 and B12:13 is blank. A8:A11 correspond to Q3 2009 to Q2 2010. Data is not available for Q3 2010 and Q4 2010 yet.

I use a similar formula to sum the last 4 quarters of revenue:
=SUM(OFFSET(INDEX(B$18:B$27,COUNT(B$18:B$27)),-3,,4))
Data is in B24:B27. A24:A27 correspond to Q1 2010 to Q4 2010.

And I will use the sum of these 2 figures to do analysis. However, it is not comparable as operating expenses data is from Q3 2009 to Q2 2010 while revenue data is from Q1 2010 to Q4 2010.

Is there any method (formula or vba but hopefully formula) to sum only the 4 quarters where data is available on both operating expenses and revenue, i.e. instead of picking up Q1 2010 to Q4 2010 reveue data, the formula picks up Q3 2009 to Q2 2010 revenue data to coincide with the data availability of Q3 2009 to Q2 2010 operating expenses data?

The reverse can also happen where revenue data is not available even though operating expenses data is available. So hopefully the formula/vba can also handle that.

Looking forward to any help I can get on this.

Thanks.

TJ

 

[home_toast]

Anyone with any idea?

Thanks.
TJ