# Thread: A conditional formatting puzzle

1. ## 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 I’d 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. 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.

3. 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. 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. 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. 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. Maude: if you wrap your formula around the [code] tags, you won't get the transcription spaces etc etc.

zeddy

8. 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

9. Unless I'm missing something (again), I think my original approach provided different colors for today's date for max and min.

10. 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

11. 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

12. 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. 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)

..and a merry Christmas to everyone

zeddy

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

MartinM (2014-12-28)

15. 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. 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 Last

#### Posting Permissions

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