Custom Formatting a number

  • Thread starter Thread starter Hari Prasadh
  • Start date Start date
H

Hari Prasadh

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.
 
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
 
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.
 
Hi Jason,

You saved the day for one of my friends!!!

Thnx a ton.
--
Regards,
Hari
India

Jason Morin said:
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


.
Click to expand...
Click to expand...
 
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 said:
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


.
Click to expand...
Click to expand...
 
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

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
Click to expand...
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


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Jason,

Thnx a ton.

--
Regards,
Hari
India

Jason Morin said:
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

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


.
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...

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 said:
Hi Jason,

Thnx a ton.

--
Regards,
Hari
India

Jason Morin said:
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

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


.



.
Click to expand...
Click to expand...
Click to expand...
 
Hi Dana,

Thnx for the post.

Actually, my requirement for custom formatting was from the angle of easier
report understanding (and not from the aspect of capturing invalid data
entries being typed in to a cell.).

Just to add to your post, I have benefited a lot from data validation
feature after reading through Debra's (www.contextures.com) pages.
Innovative use of DV.
--
Thanks a lot,
Hari
India

Dana DeLouis said:
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
Click to expand...

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 said:
Hi Jason,

Thnx a ton.

--
Regards,
Hari
India

Jason Morin said:
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

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


.



.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Number Format for text 8
#N/A Conditional Formatting affects all cells? 9
Custom Number Formats - Help 3
Number format 1
Custom numbers 1
formatting a cell 2
#VALUE error add'g formulas 1
Column sum incorrect upon subtracting 6

Back
Top