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
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
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
I read the following but still don't quite understand
[<1000]0-00;00-000. Please explain.
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
http://office.microsoft.com/en-us/excel/HP051986791033.aspx
Is a nice one, well, I think so.
