How to create a Rolling Month Formula.

**singerbatfink** · 11-18-2010, 08:11 AM

Hi All,

Im looking for a formula that looks at 2 months figures, say April & May, and works out the difference between the 2.

When new data becomes available for June and the remainder of the year I would like the formula to be able to recognise this and calculate performance based only on the last 2 months available data.

Ive attached a small data file with a scenario, if anyone would be able to have a look.

Many Thanks

B

**DonkeyOte** · 11-18-2010, 08:45 AM

If you assume 0% is never valid and you don't have 0% interspersed amongst valid data points then one possibility:

Please Login or Register  to view this content.

**singerbatfink** · 11-18-2010, 09:58 AM

Thanks DonkeyOte,

That works great.

When I apply the formula to my workbook for real Im getting #NA errors. The work book is linked to another, so cells that dont yet have any data show 0. When I delete the links the formula works? How can I amend the formula to get rid of #NA?

The rolling month figure should never be 0% but if I applied this to another scenario where it is possible to have 0% in month, how would the formula work? I thought about a Combo Box containing the months and using an OFFSET. Would that work?

Thanks in advance

B

**DonkeyOte** · 11-19-2010, 06:37 AM

Originally Posted by singerbatfink

When I apply the formula to my workbook for real Im getting #NA errors. The work book is linked to another, so cells that dont yet have any data show 0. When I delete the links the formula works? How can I amend the formula to get rid of #NA?

If the linked file is closed - unclear - then COUNTIF is precluded and you will need to revise.
However, the above issue would generate #VALUE! error rather than #N/A
The latter should only arise if the precedents contain the same error value given the pre-emptive COUNTIF prevents the LOOKUP from generating an error.

Originally Posted by singerbatfink

The rolling month figure should never be 0% but if I applied this to another scenario where it is possible to have 0% in month, how would the formula work?

You would need some means of identifying the last "active" month - if you can not do that by virtue of the figures themselves you will have to define by some other means.