Hi Paul,

Yes I did, it returns no data when I do so. I have also just tried on a blank worksheet and the result is the same. Weird though, Actuals for period 3 is showing -4132.46 but the value in that period is actually 13 678.56. When I drill down only the value for period 6 is showing in the drill down, but its not pulling as a balance for Actual Period 6.  

Hi Paul,

Hi Paul,

Yes I thought that could be the case or maybe because it ends with a 99, but the accounts are all the same for this row set.

Even worse now, if I do a range of accounts I get a figures but if I just have one account nothing is returned.  

Hi  Lincoln,

One question for you: Did you try 75.2.2.2.99 on it's own to confirm the data comes through?

The formula looks good to me but I have found cases where there are really small things that are not readily visible that cause problems with rules.

Hi  Lincoln,

This is good. We have narrowed the source of the issue.

Is your formula retrieving the account balance or the period net change?

I am not clear on your meaning about actuals versus value for the period so the preceding is a best guess based on what I would look for.

If there is no net change for the period and the report is retrieving the net change then that would explain why the account is not included.

Hi  Lincoln,

The best I can do from here is suggest you compare the account(s) that do produce a value to the account that does not.

I think there is something different about the account the is not retrieved.

Hi  Lincoln,

Without the ability to see everything it's going to be difficult to assist any further.

I would edit the formula in the formula editor and check each component until you see it return a number. I believe there's something about the formula that is causing your problem.

For example, in the image you included the 99 account is right aligned. Perhaps excel is seeing that value as something that causes the formula to not read it correctly and hence doesn't return a result for you.

If you test the different pieces of the formula manually I think you will find the source of your problem.

Hi Paul,

I finally figured it out, the way to get it correct is to always have a range. So in my case entering just the one account the formula doesn't work but if I do a range even if the account is the same then the calculation comes out fine.

In my example above I have 3 accounts in the 1st line and the range is (75.2.2.2.1 TO 75.2.2.2.2) and (75.2.2.2.99 TO 75.2.2.2.99).

So I'm sorted, thanks for your help.

Regards,

Lincoln