A place for Apptio product users to learn, connect, share and grow together.
Objective: Provide the reason why the total row for a column is showing #VALUE! instead of the actual total.
The reason why the total row for a column would show #VALUE! is because there is a non-numeric value in at least one of the rows. In the image below, the - character in the third row from the bottom is causing the problem.
To resolve the issue, the recommended method would be to re-upload the table with the corrected data.
When this occurs, I have added the below formula to correct:
Add a Formula Step
Choose the column with the issue in the formula step
Add this formula =If($_="-","0",$_)
However, if your data has 2 issues, like - or $- or $ -, does anyone know how to add an OR to this statement so it's something like this: =If($_="-"OR"$-"OR"$ - ","0",$_)? I have been unsuccessful with trying to identify multiple use cases. Thank you,
Hi Joe,
We have a request for enhancement for this functionality; identifying/calling out the cells with the non-numeric characters. It would definitely make them easier to find in larger datasets!
This is a good callout but it seems the system should just flag the row values for the user: if it knows what won't sum in a numeric column then it can apply conditional formatting if the cell value fails the numeric test to make it easier for users to see, this would help especially for larger datasets where sorting or visual inspection isn't easy. being able to pull a unique list of invalid values would also be helpful.