Max and Min

R

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
 
J

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))
 
S

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top