Addition of CUBEVALUE

[B77Wuser]

Dear all,

I have a problem with the addition of CUBEVALUEs. It may be simple, but I have not found any (elegant) solution so far.

I have a dashboard working with CUBE functions. Example: Person A is new to the company, so when I try to extract his sales, I will get an error. Person B has been working for the company for years and has made sales in the past, but not in the time period analyzed. For him, I will get the result " ".

Now when I try to add the two up (along with other employees), it will lead to another error no matter what, although the result is clearly zero.

I have tried to come up with an elegant solution, but combining IFERROR and IF/ISBLANK isn't one, especially considering that the CUBEVALUE functions are already quite substantial.

Does anyone have a more elegant approach?

Kind regards!

 

[FormR]

Hi, welcome to the forum - you could try like this.

=IFERROR(N(yourcubefunction),0)

 

[RoryA]

Welcome to the forum.

I'm not really clear as to what is wrong with IFERROR?

 

[B77Wuser]

Thank you for the warm welcome!

Indeed, the N-formula seems to work just fine! Thank you, FormR.

@RoryA: The problem is: IFERROR returns, for example, 0 for an error. A " " is not considered an error, however, so it returns " ". I don't fully understand why, but in the context of adding up CUBEVALUEs within a single cell this yields an error.

CUBEVALUE(Person A) = 0
CUBEVALUE(Person B) = " "
Cell A1 + A2 = 0

but within a single cell CUBEVALUE(Person A) + CUBEVALUE(Person B) = ERROR

 

[RoryA]

You can't add that text using the + operator (since it can't be coerced to a number), but if you use SUM it will just ignore any cells with text in them.