|
Custom Formatting a number
Hi,
I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India |
Hi Hari
i hope the cell you want "Null" or other value to come is a result of sum formula. if it is so then you can use the following formula instead of yourformula =if( yourformula=0,"NULL",yourformula) i m not very clear why you cant use it for the required purpose.if above formula cant be used please elaborate the question. Well as per my knowledge it is not possible to do with custom formatting or conditional formatting. Regards NC |
Try a custom format of:
$#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . |
Hi NC,
Its like somebody would be typing a number in to cell B4 and if that number is zero then it should get displayed in the cell as NULL ( though in the formula bar I believe it would be 0 only). if the person types a non-zero number in to B4 like 58 then it should be displayed in the cell as 58. I think I have read some similar "manipulations" in Newsgroups using formatting feature. Another way would be to use a helper cell like A4 where the person enters the number and in B4 I might have a formula like = if(A4 = 0,"Null",A4). But I dont want to do this through this method. -- Thanks a lot, Hari India wrote in message oups.com... Hi Hari i hope the cell you want "Null" or other value to come is a result of sum formula. if it is so then you can use the following formula instead of yourformula =if( yourformula=0,"NULL",yourformula) i m not very clear why you cant use it for the required purpose.if above formula cant be used please elaborate the question. Well as per my knowledge it is not possible to do with custom formatting or conditional formatting. Regards NC |
Hi Jason,
You saved the day for one of my friends!!! Thnx a ton. -- Regards, Hari India "Jason Morin" wrote in message ... Try a custom format of: $#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . |
Hi Jason,
I was in a hurry, so didnt ask in my previous post. How should I interpret or evaluate an expression like $#,##0.00_);[Red]($#,##0.00);"NULL";@ I can understand formuals written (and if not able to, I use the evaluate formula feature) but the above is greek to me. Whats the logic for these? Is there some website/link explaining about manipulating custom formats. -- Thanks a lot, Hari India "Jason Morin" wrote in message ... Try a custom format of: $#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . |
The XL help file actually does a decent job of explaining
formats. Here are some other links to help you: http://j-walk.com/ss/excel/tips/tip19.htm (download the file) http://office.microsoft.com/en- us/assistance/HP051986791033.aspx http://office.microsoft.com/en- us/assistance/HP052075951033.aspx HTH Jason Atlanta, GA -----Original Message----- Hi Jason, I was in a hurry, so didnt ask in my previous post. How should I interpret or evaluate an expression like $#,##0.00_);[Red]($#,##0.00);"NULL";@ I can understand formuals written (and if not able to, I use the evaluate formula feature) but the above is greek to me. Whats the logic for these? Is there some website/link explaining about manipulating custom formats. -- Thanks a lot, Hari India "Jason Morin" wrote in message ... Try a custom format of: $#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . . |
Hi Jason,
Thnx a ton. -- Regards, Hari India "Jason Morin" wrote in message ... The XL help file actually does a decent job of explaining formats. Here are some other links to help you: http://j-walk.com/ss/excel/tips/tip19.htm (download the file) http://office.microsoft.com/en- us/assistance/HP051986791033.aspx http://office.microsoft.com/en- us/assistance/HP052075951033.aspx HTH Jason Atlanta, GA -----Original Message----- Hi Jason, I was in a hurry, so didnt ask in my previous post. How should I interpret or evaluate an expression like $#,##0.00_);[Red]($#,##0.00);"NULL";@ I can understand formuals written (and if not able to, I use the evaluate formula feature) but the above is greek to me. Whats the logic for these? Is there some website/link explaining about manipulating custom formats. -- Thanks a lot, Hari India "Jason Morin" wrote in message ... Try a custom format of: $#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . . |
Its like somebody would be typing a number in to cell B4 and if that
number is zero then it should get displayed in the cell as NULL I know it's not what you asked, but would using Data | Validation help you by preventing zero's from being entered in the first place? One advantage would be that if someone entered a zero, then Excel could display your custom message explaining the error. (ie. "Please only enter non-zero numbers"...or whatever..) If you would like to experiment with this feature, select your range of cells, (say A1:A100) and select Data | Validation. Under the Settings tab, select "Custom" under the Allow: box. For the formula, enter =A1 < 0. It is important to note that "A1" is the first cell in your selection. Excel will correctly adjust the address for the other cells when you hit ok. For "Input Message" and "Error Alert", enter your information that explains to users what you expect. HTH -- Dana DeLouis Win XP & Office 2003 "Hari Prasadh" wrote in message ... Hi Jason, Thnx a ton. -- Regards, Hari India "Jason Morin" wrote in message ... The XL help file actually does a decent job of explaining formats. Here are some other links to help you: http://j-walk.com/ss/excel/tips/tip19.htm (download the file) http://office.microsoft.com/en- us/assistance/HP051986791033.aspx http://office.microsoft.com/en- us/assistance/HP052075951033.aspx HTH Jason Atlanta, GA -----Original Message----- Hi Jason, I was in a hurry, so didnt ask in my previous post. How should I interpret or evaluate an expression like $#,##0.00_);[Red]($#,##0.00);"NULL";@ I can understand formuals written (and if not able to, I use the evaluate formula feature) but the above is greek to me. Whats the logic for these? Is there some website/link explaining about manipulating custom formats. -- Thanks a lot, Hari India "Jason Morin" wrote in message ... Try a custom format of: $#,##0.00_);[Red]($#,##0.00);"NULL";@ HTH Jason Atlanta, GA -----Original Message----- Hi, I can do the following using a helper cell in conjunction with IF formula but I dont/cannot do it that way for the present purpose. In B4 if a cell displays zero it should display as - NULL - but if it is some number like - 32,567 - then it should display 32,567 as it is. Can this be achieved through custom formatting. Please suggest. Please note B4 contains dollar amounts. -- Thanks a lot, Hari India . . |
| All times are GMT +1. The time now is 03:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com