[AccessD] OT - Excel Find next highest number above 0

Hi Paul

Couldn't you follow this route:

  (max(value) - IIf(min(value) = 0, max(value), min(value))) /
max(value)

That would return zero if Min() is missing.

/gustav

>>> paul.hartland at fsmail.net 08/11 4:38 pm >>>
To all,

I am writing out to an Excel sheet a list of our counters names and
their count rate on a specific job (ordered by CountRate DESC) by
specific clients (9 clients, one sheet for each).  What I need to do is
get the percentage between the two extreme count rates (
(highest-lowest)/highest ) To do this I had an excel template which had
the formula ( (MAX(D2:D10000)-MIN(D2:D10000))/MAX(D2:D10000) ) and
thought this would do the job, however if we haven't got the count data
the lowest value is 0 which results in 100%....
How can I find the next highest value to the 0, I can loop backwards up
the column but thought I would ask just in case there is a much
cleaning/better way of acheiving this...

Paul Hartland
Database Developer