Max and Min
 

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

Max and Min
 

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?

Max and Min
 

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

Max and Min
 

=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

Max and Min
 

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