Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    A conditional formatting puzzle

    I'm using Excel 2003.

    I have two columns of data - one is a column of changes (which can be negative, positive or zero) and one is the date of the change. Both of these columns are named ranges.

    In a third column (also a named range), containing the data labels, I currently use Conditional Formatting to highlight the largest positive change and the largest negative change. That bit was easy, using MAX and MIN (or RANK).

    I'd prefer, though, to highlight the relevant changes only when the second column is showing today's date. That would be straightforward if I could use a helper column (well, I can do it with 2 helper columns) but Id rather not.

    This would straightforward in VBA too, but again I would rather not.

    Is it possible ? I have racked my small brain, to no avail so far !

    Thanks,

    Martin

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    For the max, I think this might work as the conditional format for all rows in the range:

    =and(b2=today(),A2=MAX((dates=TODAY())*changes))

    I did this for the MIN:

    =AND(B2=TODAY(),A2=MIN(IF(dates=TODAY(),changes,9. 99999999999999E+307)))

    In light of possible large value ties, might need the smallest number for the MAX version.

    Haven't tested that yet.
    Last edited by kweaver; 2014-12-23 at 17:05.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Martin,
    Try this formula in your conditional formatting:

    =OR(AND(VLOOKUP(MAX(Vals),$A$1:$C$8,1,FALSE)=$A1,V LOOKUP(MAX(Vals),$A$1:$C$8,2,FALSE)=TODAY()),AND(V LOOKUP(MIN(Vals),$A$1:$C$8,1,FALSE)=$A1,VLOOKUP(MI N(Vals),$A$1:$C$8,2,FALSE)=TODAY()))

    Conditional formatting1.png

    It will take your highest and lowest values in Column A and highlight them oly if the have today's date in column B. Change Vals to the named range for column A.

    HTH,
    Maud

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Note: During posting, spaces were inserted in 2 instances of VLOOKUP between the V and L And 1 instance on MIN between I and N

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks for the ideas, and please excuse me for not explaining myself perfectly

    I want to display "the largest rise (and the largest fall) which has today's date next to it".

    That isn't the same as displaying "the largest rise if it has today's date next to it".

    Sorry for the confusion - I hope that the above explanation is clearer. As you can see, what I am trying to do is difficult !

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    What if there is only one value for today's date????
    It would then be both the largest rise and the largest fall?????
    What we need is some sample representative data to understand what you want.
    It is describing the issue that is difficult.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Maude: if you wrap your formula around the [code] tags, you won't get the transcription spaces etc etc.

    zeddy

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Zeddy,
    You are right - if there has been only one change "today" the largest rise and the largest fall would be the same item ! And it wouldn't matter.
    Frustratingly I had created a sample Workbook showing the results I wanted with helper columns - but the PC it is on has just thrown a wobbly (corrupt user profile) and I am restoring as I write.
    I will post the sample when it has done.
    Ah - it has finished, and the sample is attached. I hope it makes sense - it is the item labels which are to be coloured to show the largest rise and the largest fall.
    NB the changes are always less than 100 (in either direction) hence the way I have configured the helper columns and written the formulas.
    Martin
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Unless I'm missing something (again), I think my original approach provided different colors for today's date for max and min.
    Attached Files Attached Files

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I have tweaked (rather crudely) KW's neat solution to meet a couple of conditions which I hadn't stated (in truth I hadn't thought of it, but testing highlighted the need:
    1. When all the changes are positive, I want to see the largest and smallest positive change
    2. When all the changes are negative, I want to see the largest and smallest negative change
    The number 100, which now appears in the conditional formats, is simply bigger than any anticipated change. I'm trying to think of a neater way of doing this though.

    Thanks KW - now to take a closer look at Maudibe's idea - may have to wait until after Christmas
    Attached Files Attached Files

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    In Excel2003 you can use the DMAx and DMIN functions to do what you want.
    see attached file.

    (if you paste the values from column [J] to column [C] you will see the updated data)

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2014-12-24 at 13:47.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Hi Zeddy,

    Hope you are less unwell than last Christmas !

    That's very elegant

    Any chance of getting the "helper cells" into the Conditional Format equation ? I can't see how to do that despite having experimented. I'll keep going, and see if I get there before you do !

    Martin

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    In the last year, I have drunk deep from the cup of life!
    I was in ICCU three times (Intensive Critical Care Unit) with a laparotomy, a thoracotomy, and a decortication.
    But now I am I rude health!

    Now, you can use 'named formulas' as another way of doing this.

    Create a named range for your values
    e.g. values = $c$5:$c$42

    create a named range for your dates
    e.g. dates = $d$5:$d$42


    Now define a new name as zMax.
    In the 'refers to' box, enter this:
    =MAX(IF(EXACT(dates,TODAY()),values,-10000000))
    (The final negative number -10000000 must be less than the most negative value in the datarange)

    NOTE: named formulas are treated as if they were array-entered

    Now define a new name as zMin.
    In the 'refers to' box, enter
    =MIN(IF(EXACT(dates,TODAY()),values,9999999))
    (The final number 9999999 must be higher than the most positive value in the datarange)

    see attached spreadsheet.

    ..and a merry Christmas to everyone

    zeddy
    Attached Files Attached Files

  14. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2014-12-28)

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    if you wrap your formula around the [code] tags, you won't get the transcription spaces etc
    Zeddy,

    Thanks for the tip. It was the same suggestion the RG passed to me and it does work.

    Happy holidays,
    Maud

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Yes, it was RG, but I forgot to remember that. I shall thank him again for the tip.
    When I was thinking of using the DMIN and DMAX functions, I was drinking DRUM and DCOKE, so blame my memory loss on that!

    My Caribbean trip has come to an end (flying back to UK tomorrow).
    Have a healthy and prosperous New Year to all.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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