Actual value a non-zero number but display is Zero
 

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please
Regards,
Hari
India

Hi Hari,

You are definitely on the right track with number formats! Try this:
- Select cells A1 to C1 and right-click, then select Format Cells
- Go to the Number tab and select Custom
- Type this in the format type box: "0";"0";"0"
- Click OK

Hope this helps.

Cheers,

Mark


"Hari" wrote:

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please
Regards,
Hari
India

Hi Mark,

Thanks a lot for your help.

Im able to get Zeroes in the aforementioned cells. A doubt. If instead of
writing "0";"0";"0" I write it as "0" I still able to make it work.

So whats the purpose of writing zero 3 times

Regards,
Hari
India

"Mark Hone" wrote in message
...
Hi Hari,

You are definitely on the right track with number formats! Try this:
- Select cells A1 to C1 and right-click, then select Format Cells
- Go to the Number tab and select Custom
- Type this in the format type box: "0";"0";"0"
- Click OK

Hope this helps.

Cheers,

Mark


"Hari" wrote:

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please
Regards,
Hari
India

Hi Hari,

The purpose of writing it three times is that you can apply different
formats to positive numbers (0), negative numbers (<0) and zeros (=0). If
you just had one "0" as you suggest then if someone typed in e.g. -6, that
would still display as -6 and not 0.

Admittedly the last "0" is redundant as it's for zero values anyway, but I
do like to be thorough ;-).

Cheers,

Mark


"Hari Prasadh" wrote:

Hi Mark,

Thanks a lot for your help.

Im able to get Zeroes in the aforementioned cells. A doubt. If instead of
writing "0";"0";"0" I write it as "0" I still able to make it work.

So whats the purpose of writing zero 3 times

Regards,
Hari
India

"Mark Hone" wrote in message
...
Hi Hari,

You are definitely on the right track with number formats! Try this:
- Select cells A1 to C1 and right-click, then select Format Cells
- Go to the Number tab and select Custom
- Type this in the format type box: "0";"0";"0"
- Click OK

Hope this helps.

Cheers,

Mark


"Hari" wrote:

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please
Regards,
Hari
India

Hi Mark,

Thanks a lot for such a NEAT explanation.

Couldnt have guessed it myself.

One doubt from where could I read up stuff which explains the range of
things one can achieve with the custom options.

Like how do you know that 3 "entities" could be written and first one would
apply for positive, second for negative and so on.

Regards,
Hari
India

"Mark Hone" wrote in message
...
Hi Hari,

The purpose of writing it three times is that you can apply different
formats to positive numbers (0), negative numbers (<0) and zeros (=0).
If
you just had one "0" as you suggest then if someone typed in e.g. -6, that
would still display as -6 and not 0.

Admittedly the last "0" is redundant as it's for zero values anyway, but I
do like to be thorough ;-).

Cheers,

Mark


"Hari Prasadh" wrote:

Hi Mark,

Thanks a lot for your help.

Im able to get Zeroes in the aforementioned cells. A doubt. If instead of
writing "0";"0";"0" I write it as "0" I still able to make it work.

So whats the purpose of writing zero 3 times

Regards,
Hari
India

"Mark Hone" wrote in message
...
Hi Hari,

You are definitely on the right track with number formats! Try this:
- Select cells A1 to C1 and right-click, then select Format Cells
- Go to the Number tab and select Custom
- Type this in the format type box: "0";"0";"0"
- Click OK

Hope this helps.

Cheers,

Mark


"Hari" wrote:

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please
Regards,
Hari
India

Hi Hari,

I thought I'd found this out using the application help but I can't find it
there now!

If you want to learn how to do all the clever stuff with Excel, then I'd
recommend this book: Walkenbach, J. & Underdahl, B (2001) Excel 2002 Bible
published in UK by Hungry Minds

This book covers the format of the custom number formats.

Cheers,

Mark


"Hari Prasadh" wrote:

One doubt from where could I read up stuff which explains the range of
things one can achieve with the custom options.

Like how do you know that 3 "entities" could be written and first one would
apply for positive, second for negative and so on.