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

<nnchoudhari@yahoo.com> wrote in message
news:1106220703.553046.117480@f14g2000cwb.googlegroups.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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote
in message
>news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
>> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:04a501c4fef9$8ff24e70$a301280a@phx.gbl...
> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote
> in message
> >news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
> >> 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" <excel_hari@hotmail.com> wrote in message
news:e8u4ADw$EHA.1564@TK2MSFTNGP09.phx.gbl...
> Hi Jason,
>
> Thnx a ton.
>
> --
> Regards,
> Hari
> India
>
> "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
> news:04a501c4fef9$8ff24e70$a301280a@phx.gbl...
>> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote
>> in message
>> >news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
>> >> 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 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" <delouis@bellsouth.net> wrote in message
news:uo5pNgIAFHA.1188@tk2msftngp13.phx.gbl...
>> 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" <excel_hari@hotmail.com> wrote in message
> news:e8u4ADw$EHA.1564@TK2MSFTNGP09.phx.gbl...
>> Hi Jason,
>>
>> Thnx a ton.
>>
>> --
>> Regards,
>> Hari
>> India
>>
>> "Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
>> news:04a501c4fef9$8ff24e70$a301280a@phx.gbl...
>>> 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" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote
>>> in message
>>> >news:0eaf01c4fee5$ae260670$a601280a@phx.gbl...
>>> >> 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
>>> >> >
>>> >> >
>>> >> >.
>>> >> >
>>> >
>>> >
>>> >.
>>> >
>>
>>
>
>
>