|
custom format
[<1000]0-00;00-000 for 1-22 and 12-434
I picked up the above solution from another thread. Then I created my own test. I tested with 0-00;00-000 and 00-000;0-00 respectively. No [<1000] for both. Without the condition [<1000], it will always pick up the *first* format regardless of how many digits we key or how many formats we have. That's just the way it is, right? For number formats, it will pick up the right format depending on whether the number is positive or negative. We have to enter the formats in a specific order for positive, negative etc. Is there a good link that I can use to learn more about custom format? Thanks. Epinn |
custom format
How about Help ("About custom number formats")?
In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? |
custom format
I have read that and one other link before. Still not satisfied.
*good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? |
custom format
http://office.microsoft.com/en-us/ex...986791033.aspx
Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
custom format
That link is the same as Help text and I have read it. Yes, it is okay. Thanks, anyway.
I was hoping that you could answer my other queries regarding [<1000]0-00;00-000. That was my reason for inviting you to this thread. Appreciate comments. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
custom format help appreciated
I read the following but still don't quite understand [<1000]0-00;00-000. Please explain.
Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. << Regarding [<1000]0-00;00-000, if I understand correctly, the user will key in the numbers (positive) without the dash. Custom format puts in the dash, right? According to the above, two sections = one positive and one negative format. But that's not the case for 0-00;00-000. I am missing something. Please help. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
custom format help appreciated
Hi Epinn
I know it talks about positive, negative etc., but help also talks about cell values matching different conditions. I think you need to interpret it as IF - THEN - ELSE Try using [<10000]0-00;[-100000]00-000;000-000 and then enter 1 0-01 12 0-12 123 1-23 1234 12-34 12345 12-345 123456 123-456 -- Regards Roger Govier "Epinn" wrote in message ... I read the following but still don't quite understand [<1000]0-00;00-000. Please explain. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. << Regarding [<1000]0-00;00-000, if I understand correctly, the user will key in the numbers (positive) without the dash. Custom format puts in the dash, right? According to the above, two sections = one positive and one negative format. But that's not the case for 0-00;00-000. I am missing something. Please help. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
custom format help appreciated
Read the help once more, but look at the Color and Conditions section. It
describes how to use colors, but the same technique can be used for the number format. Epinn wrote: I read the following but still don't quite understand [<1000]0-00;00-000. Please explain. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. << Regarding [<1000]0-00;00-000, if I understand correctly, the user will key in the numbers (positive) without the dash. Custom format puts in the dash, right? According to the above, two sections = one positive and one negative format. But that's not the case for 0-00;00-000. I am missing something. Please help. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson -- Dave Peterson |
custom format help appreciated
Thank you both for responding. Prior to posting, I had no problem understanding the two features *separately* - positive/negative/zero and conditional. I also understand that I should interpret the format as IF-THEN-ELSE. I only have a problem when I try to put everything together and see the big picture. I have a feeling that no matter how many times I read, I may not have the answer because the missing piece of my puzzle is not spelt out. I can only experiment to find out. Maybe I didn't ask the right question. I'll try again.
I understand Roger's example well and I know I have to interpret the format as conditional. If you re-read my very first post in this thread, you would have noted that I played with [<1000]0-00;00-000. When I took out [<1000], the numbers I keyed in always picked up the first format. If I keyed in 12345 it wouldn't match the number of digits and picked up 00-000 format. I failed to see the big picture because my mind was still with conditional. My assumption is as soon as I took out [ ], the definition *automatically* turned into sectional. If I had keyed in a negative number it would have picked up the second format. This was the answer I was looking for. After more experiments, I have come to a conclusion. It appears that the semicolon (;) is used as an delimiter for *both* conditional and positive/negative/zero features but not in the *same* definition. I cannot see how Excel can tell when the ; is a break for a section or a break for a condition in the *same* definition. I think the answer is it cannot differentiate and it doesn't have to. Why? It is an either/or situation. The format is either conditional *OR* sectional (i.e. positive, negative, zero etc.) We cannot have sections and then within a section different conditions (=, , < etc.) right? If I am wrong, then I am having a problem seeing how Excel can tell when ; means section end and when condition change within the *same* definition. ***Please confirm that as soon as Excel sees a pair of [ ], the entire definition will be treated as conditional and the sectional feature is overridden.*** [blue][100];[red] translates to blue if 100 and anything less (including 100) is red. It does not translate to 100 is blue and *negative* is red. The semicolon in this case is not a delimiter for sections (i.e. positive and negative). This is in line with my theory that once [ ] is found the entire definition is treated as conditional. I think I have solved my puzzle. Thank you for providing the stimulation. May I remind you that I have a unique way of interpreting things and I am detailed. <bg By the way, I find this forum less crowded, calmer and more peaceful than the other one. Maybe I'll switch back. My focus is on learning but when I encounter certain issues I can't resist speaking out. It'll be great if you all can drop by and visit me some time. Have a good weekend. Epinn "Roger Govier" wrote in message ... Hi Epinn I know it talks about positive, negative etc., but help also talks about cell values matching different conditions. I think you need to interpret it as IF - THEN - ELSE Try using [<10000]0-00;[-100000]00-000;000-000 and then enter 1 0-01 12 0-12 123 1-23 1234 12-34 12345 12-345 123456 123-456 -- Regards Roger Govier "Epinn" wrote in message ... I read the following but still don't quite understand [<1000]0-00;00-000. Please explain. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. << Regarding [<1000]0-00;00-000, if I understand correctly, the user will key in the numbers (positive) without the dash. Custom format puts in the dash, right? According to the above, two sections = one positive and one negative format. But that's not the case for 0-00;00-000. I am missing something. Please help. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
Please also read this before you reply.
I have a different perspective and would like to rephrase the conclusion of my findings.
Semicolon (;) is the delimiter for conditions period. A pair of [ ] allows us to specify the conditions we want. If not found, the *default* (i.e. positive, negative, zero, text) will be used. I think this is it - no such thing as differentiation between sectional and conditional. I did a good job in confusing myself. <bg The following is Epinn answering Epinn's very first post in this thread. Without the condition [<1000], it will always pick up the *first* format regardless of how many digits we key or how many formats we have. That's just the way it is, right? << As soon as you take out [<1000], Excel will use the default conditions i.e. positive, negative, zero and text respectively. Semicolon is used as the delimiter for the four different sections. It only picks up the first format because you probably use positive numbers only. Try to use a negative number, and you'll see that the second format will be used. This is *the* answer I was desperate to find. Thank you for reading. Epinn "Epinn" wrote in message ... Thank you both for responding. Prior to posting, I had no problem understanding the two features *separately* - positive/negative/zero and conditional. I also understand that I should interpret the format as IF-THEN-ELSE. I only have a problem when I try to put everything together and see the big picture. I have a feeling that no matter how many times I read, I may not have the answer because the missing piece of my puzzle is not spelt out. I can only experiment to find out. Maybe I didn't ask the right question. I'll try again. I understand Roger's example well and I know I have to interpret the format as conditional. If you re-read my very first post in this thread, you would have noted that I played with [<1000]0-00;00-000. When I took out [<1000], the numbers I keyed in always picked up the first format. If I keyed in 12345 it wouldn't match the number of digits and picked up 00-000 format. I failed to see the big picture because my mind was still with conditional. My assumption is as soon as I took out [ ], the definition *automatically* turned into sectional. If I had keyed in a negative number it would have picked up the second format. This was the answer I was looking for. After more experiments, I have come to a conclusion. It appears that the semicolon (;) is used as an delimiter for *both* conditional and positive/negative/zero features but not in the *same* definition. I cannot see how Excel can tell when the ; is a break for a section or a break for a condition in the *same* definition. I think the answer is it cannot differentiate and it doesn't have to. Why? It is an either/or situation. The format is either conditional *OR* sectional (i.e. positive, negative, zero etc.) We cannot have sections and then within a section different conditions (=, , < etc.) right? If I am wrong, then I am having a problem seeing how Excel can tell when ; means section end and when condition change within the *same* definition. ***Please confirm that as soon as Excel sees a pair of [ ], the entire definition will be treated as conditional and the sectional feature is overridden.*** [blue][100];[red] translates to blue if 100 and anything less (including 100) is red. It does not translate to 100 is blue and *negative* is red. The semicolon in this case is not a delimiter for sections (i.e. positive and negative). This is in line with my theory that once [ ] is found the entire definition is treated as conditional. I think I have solved my puzzle. Thank you for providing the stimulation. May I remind you that I have a unique way of interpreting things and I am detailed. <bg By the way, I find this forum less crowded, calmer and more peaceful than the other one. Maybe I'll switch back. My focus is on learning but when I encounter certain issues I can't resist speaking out. It'll be great if you all can drop by and visit me some time. Have a good weekend. Epinn "Roger Govier" wrote in message ... Hi Epinn I know it talks about positive, negative etc., but help also talks about cell values matching different conditions. I think you need to interpret it as IF - THEN - ELSE Try using [<10000]0-00;[-100000]00-000;000-000 and then enter 1 0-01 12 0-12 123 1-23 1234 12-34 12345 12-345 123456 123-456 -- Regards Roger Govier "Epinn" wrote in message ... I read the following but still don't quite understand [<1000]0-00;00-000. Please explain. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. << Regarding [<1000]0-00;00-000, if I understand correctly, the user will key in the numbers (positive) without the dash. Custom format puts in the dash, right? According to the above, two sections = one positive and one negative format. But that's not the case for 0-00;00-000. I am missing something. Please help. Epinn "Dave Peterson" wrote in message ... http://office.microsoft.com/en-us/ex...986791033.aspx Is a nice one, well, I think so. Epinn wrote: I have read that and one other link before. Still not satisfied. *good* link Epinn "JE McGimpsey" wrote in message ... How about Help ("About custom number formats")? In article , "Epinn" wrote: Is there a good link that I can use to learn more about custom format? -- Dave Peterson |
| All times are GMT +1. The time now is 08:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com