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

So presumably you have a query that selects one client's counters and sorts
them by the count rate and it is the results of this query that is being
output to Excel. Something like this...

SELECT tblCounters.nCountRate 
FROM tblCounters
WHERE (((tblCounters.nClientID)=1))
ORDER BY tblCounters.nCountRate DESC;

Say this is qryCountRates

You could just as easily write a Totals query to do the calculation of the
percentage. This query would use the first query as its data source, and it
would have a criteria that would specify that the count rate must be > 0.

Like this...

SELECT Max(qryCountRates.nCountRate) AS MaxNo, Min(qryCountRates.nCountRate)
AS MinNo, ([MaxNo]-[MinNo])/[MaxNo] AS Perc
FROM qryCountRates
WHERE (((qryCountRates.nCountRate)>0));

This (with some dummy data I cobbled together) produced results like this in
just a few seconds: probably much faster than running a custom VBA function
thousands of times.

MaxNo	MinNo	Perc
100000	58	0.999419987201691

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, August 11, 2005 10:38 AM
To: accessd
Subject: [AccessD] OT - Excel Find next highest number above 0


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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com