• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number formatting - show 12000 as 12, 700 as 0.7, -12000 as (red)-12 and -700 as (red) -0.7

victorpw

New Member
Hi

Appreciate is someone could assist with Excel number formatting.

I hope to be able to format
12000 as 12
700 as 0.7
-12000 as -12 (in red)
-700 as -0.7 (in red)

I tried using several number formats
  • #,##0,_);[Red](#,##0,)
  • [>=1000]#,##0,;[<1000]0.0,
but am unable to get the desired result (column J in Excel worksheet).
I hope to achieve results without need to use Conditional Formatting.

Thanks.

Regards
Victor
 

Attachments

  • Number format - format numbers to red for negatives.xlsx
    9.1 KB · Views: 0
I don't think there is way to do this with formatting cell only.

Alternate solution:
Use IF & TEXT function to apply number format you desire and then use conditional format to colour it.

See attached for example.

P.S. You should be able to do it with Conditional format alone, but if you have large data range it is going to impact performance. More so than nested IFs.
 

Attachments

  • Number format - Condformat.xlsx
    9.4 KB · Views: 0
Hi Chihiro

Many thanks for your contribution.

Your formula managed to achieve the desired "display" result but it appears to also convert the number to text - which mean we will not be able to sum the results. (see attached, sum @ J14 = 0, instead of 2)

You may be right - I read somewhere that Excel allows only 3 conditions to the number formatting - but to achieve the desired results (decimals only for values between+999 and -999) and red colour for negative numbers will require more conditions than 3.

Looks like I have to use conditional formatting to achieve my goal.

If anyone has a solution to the problem, appreciate very much your help/guidance.

Regards
Victor



I don't think there is way to do this with formatting cell only.

Alternate solution:
Use IF & TEXT function to apply number format you desire and then use conditional format to colour it.

See attached for example.

P.S. You should be able to do it with Conditional format alone, but if you have large data range it is going to impact performance. More so than nested IFs.
 

Attachments

  • Number format - Condformat (2).xlsx
    10.9 KB · Views: 0
Last edited by a moderator:
To add the Text values, you can use VALUE function to return them to number. Or alternately keep the original and use that to do calculation. Using formatted column to display results only.
 
Back
Top