Trying to Acheive an Average LTM in Cost Transparency and need to Excludes BLANKS

Question

Hello, I am working on a blended rate for our contractors....while it has been a couple week challenge, I am 90% there....the CIO wants a 12 month rolling average. So I started off with this in metrics:
=(TimePeriod(Blended Rate L2, -11) + TimePeriod(Blended Rate L2, -10) +
TimePeriod(Blended Rate L2, -9) + TimePeriod(Blended Rate L2, -8) +
TimePeriod(Blended Rate L2, -7) + TimePeriod(Blended Rate L2, -6) +
TimePeriod(Blended Rate L2, -5) + TimePeriod(Blended Rate L2, -4) +
TimePeriod(Blended Rate L2, -3) + TimePeriod(Blended Rate L2, -2) +
TimePeriod(Blended Rate L2, -1) + Blended Rate L2)/12

But then realized that dividing by 12 did not give the correct result because of random blanks in lines through the months (One leader may have a rate every month, some quarterly, some only once a year) so I need to exclude the blanks and only divide by the values that are there, something like this:
=IF(Blended Rate L1!="",
(
IF(TimePeriod(Blended Rate L1, -11)!="", TimePeriod(Blended Rate L1, -11), 0) +
IF(TimePeriod(Blended Rate L1, -10)!="", TimePeriod(Blended Rate L1, -10), 0) +
IF(TimePeriod(Blended Rate L1, -9)!="", TimePeriod(Blended Rate L1, -9), 0) +
IF(TimePeriod(Blended Rate L1, -8)!="", TimePeriod(Blended Rate L1, -8), 0) +
IF(TimePeriod(Blended Rate L1, -7)!="", TimePeriod(Blended Rate L1, -7), 0) +
IF(TimePeriod(Blended Rate L1, -6)!="", TimePeriod(Blended Rate L1, -6), 0) +
IF(TimePeriod(Blended Rate L1, -5)!="", TimePeriod(Blended Rate L1, -5), 0) +
IF(TimePeriod(Blended Rate L1, -4)!="", TimePeriod(Blended Rate L1, -4), 0) +
IF(TimePeriod(Blended Rate L1, -3)!="", TimePeriod(Blended Rate L1, -3), 0) +
IF(TimePeriod(Blended Rate L1, -2)!="", TimePeriod(Blended Rate L1, -2), 0) +
IF(TimePeriod(Blended Rate L1, -1)!="", TimePeriod(Blended Rate L1, -1), 0) +
IF(Blended Rate L1!="", Blended Rate L1, 0)
) /
MAX(
IF(TimePeriod(Blended Rate L1, -11)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -10)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -9)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -8)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -7)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -6)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -5)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -4)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -3)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -2)!="", 1, 0) +
IF(TimePeriod(Blended Rate L1, -1)!="", 1, 0) +
IF(Blended Rate L1!="", 1, 0),
1)
)
BUT the Apptio doesn't like that formula. Can someone please help???

Accepted Answer

Testing this will take a while, but I would guess that the engine cannot handle such a complex formula, with so many ifs. I'll try the following:

Have two TEMP columns with the two halves of the formula
If it doesn't work, I'll chunk it down further (one column per "If"

IBM Apptio