Number format for -1E-100

[Nuz]

I have a range of cells where some cells have values -1E-100.
I would like to display those values like they are zeros. But since they are actually negative values, my custom number format is not working correctly. I currenly have the number format as:

_-* # ##0_-;_-* (# ##0)_-;_-* -_-;_-@_-

The problem is that -1E-100 is displayed as (0) when I would like to have it displayed as - (like a zero value).

Can I modify the custom number formatting to fix this for -1E-100 values?

 

[xenou]

Correct me if I'm wrong but this is an impossibly minute (or large - after its been negated) number that would have 100 zeros in it ... it's not the kind of thing that makes any sense without scientific notation. I don't think Excel can actually display numbers with more than 15 digits of precision - unless you convert it to text, which then ruins the mathematical value of your data. Nobody could really understand such a number anyway.

 

[mikerickson]

Perhaps a custom format like this would serve

[<-1E-100]_-* (# ##0)_-;[<=1E-100]-;_-* # ##0_-

 

[Nuz]

That works well, other than it displays -1E-100 as -- rather than - .

It currently displays values as:

0 --> - (This is ok)
-1E-100 --> -- (Should be displayed as -)
500000 --> 500 000 (This is ok)
-500000 --> (500 000) (This is ok)

I tried to modify the format but couldn't find a feasible solution.
Any idea how to fix that?

 

[mikerickson]

I can't find a way to avoid that either.

Actually, I have to go with Xenou on this point. E-100 is far to small a number for Excel to deal with. One should put a ROUND in the formula chain so that E-100 becomes 0.

 

[Nuz]

Unfortunately, I can't round it to 0. I have XIRR formula that takes these values and it seems that XIRR doesn't calculate IRR correctly if there are leading zero values before the actual values start (this is the other topic in itself). I found a solution to fix that by inserting -1E-100 for the values to be ignored and 0 for the dates to be ignored and the formula then calculates correctly.

But for the user, it is annoying to display -1E-100 values as they are.

 

[mikerickson]

If it's needed to get the right answer, the user can live with --.

Or, to put it another way, your spreadsheet has the feature of distinguishing between "negligable" and "nothing" while making them both visually insignificant.

 

[Nuz]

Yeah, you're right.
It's actually possible to use conditional formatting on those cells on top of the custom number format so that the display is "-" with all those values.

But thanks for the answer. It got me forward with this.

 

[Norie]

If this minute number is only going to be used in a formula does it really need to be displayed?

 

[Nuz]

Yes, they have to be displayed, and they should be displayed with the same format as zero values are displayed. Input values are cash flows where the IRR is calculated. Cash flows have to be displayed at all times and should be displayed as cleanly as possible.