Max and Min

[RB]

Hello,

I have a column that has the sales for each customer. I want to know
the max and min values for each of these. However, some sales are
negative values and some are zeroes. But, I want to exclude those
values and use only the positive values for max and min. Can somebody
help me with this?

Thanks,

RB

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-Enter or CMD-RETURN):


=MAX(IF(A1:A100>0,A1:A100))
=MIN(IF(A1:A100>0,A1:A100))

 

[JP]

Check out

http://www.cpearson.com/excel/lists.htm

HTH,
JP

 

[Sandy Mann]

=MIN(IF(A1:A6>0,A1:A6))

Entered as an array formula with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Presumably you have two columns then - one for customer (assume A) and
another for sales (assume B). If you list all your customers in column
C, and use column D for the maximum and column E for the minimum, then
you can put these array* formulae in the cells stated:

D1: =MAX(IF((A$1:A$200=C1)*(B$1:B$200>0),B$1:B$200))
E1: =MIN(IF((A$1:A$200=C1)*(B$1:B$200>0),B$1:B$200,10E10))

I've assumed that you have data in rows 1 to 200, so adjust the range
references to suit (but you can't use full column references prior to
Excel 2007). Copy the formulae down to cover your customers in column
C.

* As these are array formulae then once you have typed them in (and if
you subsequently amend them) you need to use CTRL-SHIFT-ENTER (CSE) to
commit them rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - do not type these yourself.

Hope this helps.

Pete