Can't change format in Data Model because of some Iferror -> "" values

[MrPokemans]

Hi,


I'm working with a large data set. One column calculates the age of the customer at the moment of payment. The formula contains the IFERROR(...........,"") because of a number of birth dates and payment days missing. This formula has to stay in place because of frequent refreshing of the data.

Because of the empty valued cells with "" (so not really empty), the data model considers them as text. I'm not allowed to change the format in the data model to anything other than text.

As a result, the Power Pivot Table can only 'count' the values instead of summing up etc. But that's the problem, I need to make calculations and have them sorted from 0 to 20 (instead of 0-1-10-11-12 etc.). How can I turn this format to numbers?

I can't share the file but I can replicate something if it would help.

Thanks!

Joris

 

[Yongle]

Are you allowed to use this formula
IFERROR(...........,0)

To suppress zero values

Pro tip: Three ways to hide zero values in an Excel sheet | TechRepublic

Although zero values are valid, you might need to hide them. Here are three methods for hiding zero values in an Excel sheet.

www.techrepublic.com

 

[MrPokemans]

Thanks! But no. With IFERROR(.....,0) all my customers with errors will be age 0. And because there are also 'real' baby clients (health care), these would get lost in the filters.

With IFERROR(.....,"") the data model considers the 'empty' cells as text and according to the data model, the whole column is text.

I think the best solution would be IFERROR(.......,9999) in this case, and then filter out all the 9999-year olds. Though it's not the cleanest solution.

Thanks for thinking along anyway!