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

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


=MAX(IF(A1:A1000,A1:A100))
=MIN(IF(A1:A1000,A1:A100))

In article
,
RB wrote:

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?

Check out

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

HTH,
JP

On Mar 20, 12:22*pm, RB wrote:
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

=MIN(IF(A1:A60,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


Replace @mailinator.com with @tiscali.co.uk


"RB" wrote in message
...
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

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$2000),B$1:B$200))
E1: =MIN(IF((A$1:A$200=C1)*(B$1:B$2000),B$1:B$200,10E 10))

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


On Mar 20, 4:22*pm, RB wrote:
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