excel custom number format with three conditions

Question

is there a possibility to use multiple conditions in Excel number format?

I need following:

 number    format

  7000      7
  125       0
 -8054     (8)
 -149      (0)

when I use:

#,###, ;(#,###,)

it works well unless number is less then -499, which is displayed as (0)

I've tried

[>=0]#,###, ;[>-500]("0");(#,###,)

and that works fine, but the problem is when the number less then 500 0 is not displayed.

So I've tried:

[<500],"0" ;[>=0]#,###, ;[>-500]("0");(#,###,)

but excel cannot accept this numbering format. Any ideas?

Thanks a lot in advance!!!

Answer 1

While Jean-François Corbett has the correct answer for the question's specific case, it does not answer the titular question of the OP.

It seems that Excel (and related spreadsheet software) has a fundamental limitation that it can only use two conditionals in custom number formats. Apparently, internally it is using 4 possible display categories: number condition 1, number condition 2, other number, and text. Setting custom number formats can only redefine the two number conditions. You cannot directly control the "other" condition.

So, a possibly more clear way of describing the conditional options for custom formatting:

[condition 1]custom format 1;[condition 2]custom format 2;other number format;text format

Where condition 1 defaults to >0 and condition 2 defaults to <0.

Answer 2

This works for me:

#,##0,;(#,##0,)