Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional Format on Course (Excel XP)

    Hi,
    Here is a file that formats each alternate row to read the rows easier. How can I conditional format so that records are hi-lighted by Course! This will make grouping each course easier to view!
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    If the table was sorted on Course, it wouldn't be really difficult, but since it is sorted on another column, I don't see an easy solution. I'll have to think about this.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    The conditional format can be set up on the Start Date field too! It is sorted by the Start Date! The idea is just to colour code the groups of courses!
    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    Select B2:G43.
    Select Format | Conditional Formatting...
    In the Formula Is box, enter this formula:
    <code>
    =MOD(SUM(1/COUNTIF($D$2:$D2,$D$2:$D2)),2)>0.5
    </code>
    and click OK. You'll have alternating colors based on the start date.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    Hans,
    You have the idea, but it is not working 100%. It is not including the last record of the course and starts on a new course by selecting the last of the previous course. (Boy, that does not make sense if you read it fast!!!)
    I copied your formula into the conditional formatting. Can you check your result please!!!

    Tx
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    It works fine for me. See the attached version. Did you start the selection in row 1 instead of in row 2?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    Sorry, I did start in row 1. I have a habit of using CTRL+* to select tables of data!
    Thanx...that sorted it out!
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    You could apply the conditional format to the whole table (including row 1), you just need to replace $D$2:$D2 with $D$1:$D1 in the formula.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    OK...great, that will suit me better. Tx for this formula...it looks interesting. I'm going to use the formula evaluator in Excel to study this one up!
    Cheers
    Regards,
    Rudi

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    Hans,
    I've been studying up this formula. (It is doing a great jog for me currently! - Tx)

    I used the Evaluate formula feature to step through the formula! I notice that it sums the result of 1/Count of Date...(which shows 12's and 2's and 1's, as you step through it). Eventually it Moderates the values and tests for >0.5!

    How did you work it out. How in the world do you create something that does what I need???? Can you tell me how you went about developing this nested function.

    Tx
    Regards,
    Rudi

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    The formula =SUM(1/COUNTIF(range,range)) counts the number of unique entries in range. for example, in D6, the part

    SUM(1/COUNTIF($D$2:$D6,$D$2:$D6))

    counts the number of unique entries in D26. As you move down the column, this number increases by 1 each time the date changes. To get alternating colors, we take the number modulo 2, i.e. the remainder after division by 2. Theoretically, the result is either 0 or 1, but due to rounding errors it can be slightly off. That's why I tested for MOD(...) > 0.5 instead of MOD(...) = 1.

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    Yes...I noticed the long decimaled values appear in the formula evaluator!
    I tested the function by breaking it up into the induvidual functions....
    - CountIf counts 1,2,3,... with the same date...starting at 1 with the new date...
    - 1/CountIf changes it to 1, 0.5, 0.33333, 0.25......
    - I don't understand why you sum the result... (This may be due to the array that you are using?)

    The last Q! : How does the formula change the first date that evaluated to 1 to FALSE?? ( 1 / 1 = 1) - How does this evaluate to FALSE / TRUE in the first date of the group?

    I hope I'm clear. (Sorry for keeping you busy today!)
    Regards,
    Rudi

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format on Course (Excel XP)

    See the attached workbook, where I pulled the formula apart.
    In the range A1:A10, the value 1 occurs twice (in A1 and A2). So COUNTIF(A1:A10,1) returns 2, and 1/COUNTIF(A1:A10,1) returns 1/2 = 0.5.
    In B1:B10, you'll see 0.5 twice, in B1 and B2. Added, this results in 1.
    Similarly, the value 2 occurs thrice (in A3, A4 and A5). So COUNTIF(A1:A10,2) returns 3, and 1/COUNTIF(A1:A10,2) returns 1/3 = 0.3333.
    In B1:B10, you'll see 0.3333 thrice, in B3, B4 and B5. Added, this results in 1.
    As you see, each unique value contributes 1 when you sum all these values. In other words, the sum is precisely the number of unique values in A1:A10.

    I don't understand your second question.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Format on Course (Excel XP)

    Don't worry...your explanation has cleared up my second question too!

    I understand fully now!
    Many many TX
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •