Paul Hartland (ISHARP)
paul.hartland at isharp.co.uk
Thu Aug 11 10:24:10 CDT 2005
Not really, I need to calculate the two extreme values, so say if Row12 ColumnD =0 I would have to got to Row11 ColumnD and see that value, if that was also a zero then row 10, 9, 8 etc until I reach a cell with a value inside -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: 11 August 2005 16:16 To: accessd at databaseadvisors.com Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com