CODEWITHSUNDEEP
excelexcel-formulaformattingnumber-formattingexcel-2013
FD Kapital
FD Kapital

Reputation: 31

Excel custom number format complex conditions

I need following:

My numbers are to be divided by 1000 and rounded, negative in brackets, zeroes as "-":

  1500 ->  2
  499  ->  -
  0    ->  -
 -499  ->  -
 -1500 -> (2)

I cannot find how to transform values [-499;499] to "-", everything else is fine.

Currently (simplified) I have smth like this: "#,##0, ;(#,##0,);- "

It displays my numbers as:

  1500 ->  2
  499  ->  0 (I need "-" here)
  0    ->  -
 -499  -> (0)(I need "-" here)
 -1500 -> (2)

Any ideas? Thanks!

Upvotes: 1

Views: 1117

Answers (2)

FD Kapital
FD Kapital

Reputation: 31

Found the solution:

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

Displays my numbers as:

 1500       ->  2
 499        ->  -
 0          ->  -
-499        ->  -
-1500       -> (2)
 blank cell -> blank cell
 text       -> text

Main problem was how to display -499 as "-"

as Jeeped posted

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

did this:

 1500       ->  2
 499        ->  -
 0          ->  -
-499        ->  -- (here was the problem)
-1500       -> (2)
 blank cell -> blank cell
 text       -> text

Upvotes: 2

yass
yass

Reputation: 869

You can use a formula and formatting, in B1: 

=IF(ROUND(A1/1000,0)<>0,ROUND(A1/1000,0),"-")
  • Number
  • Format Cells
  • Number (under category)
  • Choose (123) and 0 for decimal places
  • Ok

And drag it down

Another way:
Use the formula in B1: 

=ROUND(A1/1000,0)

Drag it down, select the result and Custom Format with:

0;(0);-

  • Number
  • Format Cells
  • Custom (under category)
  • Write in the Box: 0;(0);-
  • Ok

Upvotes: 0

Related Questions