MIN, MAX, MODE tec.... from an array???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jedit - 02 Jul 2007 09:54 GMT

Hi All
I’ve seen this so many times but none include a function for blank cells?

I’m trying to find the min, max, mode, median etc…. for a list of values
given an IDL (CELL F12) to look up.

The data range is B14 to H1574 and the columns headings are
IDS (B13), Yield (G13), UTS (H13)

What I’m trying to do is find the MIN value for Yield where the look up ID
(IDL)
Equals IDS.
I cam up with the following formula but it returns 0 as there are some rows
with blank cells?

MIN (IF($B$14:$B$1574=F12,G$14:G$1574))

How then do I find the min value omitting the blank ones?

Reply to this Message

Bob Phillips - 02 Jul 2007 10:24 GMT

Try this variation

=MIN(IF(($B$14:$B$1574=F12)*(G$14:G$1574<>0),G$14:G$1574))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi All
> I've seen this so many times but none include a function for blank cells?
[quoted text clipped - 15 lines]
>
> How then do I find the min value omitting the blank ones?

Reply to this Message

Rate this thread: