• 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.

Format Cell Number to Millions

P

PatsyG

New Member
Hi,

I have data which is coming in thousands and need to format to million so using #,###, as the formats. For data which is negative I am getting -11 but need brackets like (11) so using #,###,;(#,###,). However this gives me another problem, data which is negative and the return should be blank the result is (). Can anyone help me format this so it's blank?
 
Hi PatsyG


Firstly, welcome to the Chandoo.org Forums


Give the following a go


#,###,;(#,###,);;
 
Thanks for suggestion Hui but not working, still getting (). The data set is for example -362. Been trying a few other formats adding [<1000000] but no joy.
 
Hi Patsy


I do not know whether the , at the end of the format is intentional or not. What it does , is round off the displayed value to as many digits as there are '#' signs in the format.


Thus , if the format is #,###, and the entered value is 1234567 , the displayed value is 1,235 !


If you did not intend this , change the format to #,##0;(#,##0) , which would display as follows :


# (number sign) displays only significant digits and does not display insignificant zeros.

0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.


The () are displayed because the -362 is being rounded off to 0 !


Try by entering -3622 , and you will see (4) being displayed !


http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx
 
Hi Narayank991,

This is intentional, for example data coming in is -11382, I need it to be -11.

However for another data it is -362 but with my formats it's coming in as () and I want it to be blank
 
Hi Patsy ,


Thanks for the clarification.


I do not know how to eliminate the () because they are part of the format , for negative numbers. By changing the negative format to (#,##0,) , you should be seeing (0) , which may be better than (). If you do not want negative numbers to be enclosed in brackets , then using #,###, would not display the ().


Narayan
 
Hi Narayank991,

It seems I can only get one or the other! There must be a way around this, I will keep perservering!


Thanks
 
What about

[>1000]#,###,;[<-1000](#,###,);;

362 will return (blank)

-362 returns -
 
Hi Hui

Brilliant it works! I was trying something similiar but it's the ? which has me baffled. Do you know what this does? I like to translate the formulas to laymans terms as I will more likely remember them this way!


Fantastic!
 
[<-1000](#,###,);[<1000]?,;#,###,

Comprises 3 Custom Number formats separated by ;


[<-1000](#,###,)

If the number is less than -1000 use the format (#,###,)

ie: (No. divide 1000 inside brackets)

If true stop


[<1000]?,

If the number is less than 1000 use the format ?,

ie: No. divide 1000, the ? is a digit Digit placeholder.

This code leaves a space for insignificant zeros but does not display them.

If true stop


#,###,

If the number is greater than 1000 use the format #,###,

ie: No. divide 1000


For more help on Custom Number Formats refer: http://www.ozgrid.com/Excel/excel-custom-number-formats.htm
 
You must log in or register to reply here.
Back
Top