Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display ONLY fraction portion of number | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
How do I subtract with text valued number instead of an actual num | Excel Worksheet Functions | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions | |||
Axis display as category | Charts and Charting in Excel |