Recording highest and lowest ranking

[Guest]

I have a ranking system for a range of products. They are automatically
ranked each day to show which sells the best and which sells the worst. In a
product information sheet I have a cell which displays "Ranking" it takes the
ranking from a look up table. But in two other cells I would like to record
the highest a products rankign has ever been and the lowest it has ever been.
So basically if the ranking is in cell A1, the value in cell A2 (the highest
ever ranking) has to be If A1 < A2, then A2 = A1, but if A1>A2 then A2
doesn't change. I need to do this for around 14 products. Is this possible?

 

[Tom Ogilvy]

You can use intentional circular references, but then you would have to
allow this at the application level.


I would just run a macro that does the comparison and the update

Sub ABC()
Dim rng1 as Range, rng2 as Range, rng3 as Range
set rng1 = Range("A1")
set rng2 = Range("A2") ' holds min value
set rng3 = Range("A3") ' holds max value
if rng1 < rng2 then rng2 = rng1
if rng1 > rng3 then rng3 = rng1
End Sub


You need to prime A2 and A3 with extreme values - A2 a high number and A3 a
Low number