BPC MS EPM SP 17 Patch 2 Local member returns #DIV/0! error

[Former Member]

All,

I am working with EPM MS for BPC and All of my local formula works.

But some times when there is no value in the forecast or budget, the rows come back with #DIV/0! error.

The problem is #DIV/0! in the report.

What I have Done

The formula I am using is

=EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[BUDGET])/EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[ACTUAL])/100

It Should be

I tried using IF and ISERROR formula like below and it dose not work.

=IF(ISERROR(EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[BUDGET])/EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[ACTUAL])/100),"",EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[BUDGET])/EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[ACTUAL])/100)

Anyone came across this problem and how did you solved it? I do not want to use local excel formulas as it is not an option to what I am trying to do.

Thanks your for your help.

Su

[Former Member]

Hi,

I don't know how you have built your report, but maybe if you used XL cell references instead of the tuple?

So something like

=IF(b2=0,"",c2/b2)

Or maybe

=IF(EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[ACTUAL])=0,"",EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[BUDGET])/EPMTUPLE([TIME].[H1].[2014.Q1];[CATEGORY].[H1].[ACTUAL])/100)

BR,

Arnold

[Former Member]

Arnold,

Thank you for your response, The EPM and Excel function does not seem to work together.

The basic Excel function would work however that is not an option in my case, I have to use EPM function as EXCEL function local member static with location formula.

If there is a way to make excel function dynamic please advise.

I tried every which way including your suggestion, It is odd that EPM function gives EXCEL errors or has no options to turn them off.

I was hoping there is a way to use EPM and not return any errors.

Su