Date Range
 

If I have a column of #'s and i want a formula to tell me what is the least #
in that column (to be put on another page) how would I do that? To be more
specific, the formula i need is:

rows in detail from 1-4000

if column I = advertising and
if column Q is not empty
then tell me of those in the range, what is the lowest #

also need for highest #

Date Range
 

=MIN(Q1:Q4000)
=MAX(Q1:Q4000)

"Karen Smith" wrote in message
...
If I have a column of #'s and i want a formula to tell me what is the
least #
in that column (to be put on another page) how would I do that? To be
more
specific, the formula i need is:

rows in detail from 1-4000

if column I = advertising and
if column Q is not empty
then tell me of those in the range, what is the lowest #

also need for highest #

Date Range
 

but I need the first part of the formula to read, if column I = advertising &
column Q is more than 0, than tell me max # for column P. Any ideas?

"Bob Umlas" wrote:

=MIN(Q1:Q4000)
=MAX(Q1:Q4000)

"Karen Smith" wrote in message
...
If I have a column of #'s and i want a formula to tell me what is the
least #
in that column (to be put on another page) how would I do that? To be
more
specific, the formula i need is:

rows in detail from 1-4000

if column I = advertising and
if column Q is not empty
then tell me of those in the range, what is the lowest #

also need for highest #

Date Range
 

Try this array* formula:

=MAX(IF((I1:I4000="advertising")*(Q1:Q40000),P1:P 4000,0))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) instead of
just ENTER. If you do this correctly, then Excel will add curly braces
{ } around the formula when viewed in the formula bar - you must not
type these yourself.

Similar formula for MIN, except that you need a very large number at
the end instead of zero, i.e.:

=MIN(IF((I1:I4000="advertising")*(Q1:Q40000),P1:P 4000,100000000))

Again, CSE to commit. If your numbers are larger than the one I've
given, add a few zeros.

Hope this helps.

Pete

Karen Smith wrote:
but I need the first part of the formula to read, if column I = advertising &
column Q is more than 0, than tell me max # for column P. Any ideas?

"Bob Umlas" wrote:

=MIN(Q1:Q4000)
=MAX(Q1:Q4000)

"Karen Smith" wrote in message
...
If I have a column of #'s and i want a formula to tell me what is the
least #
in that column (to be put on another page) how would I do that? To be
more
specific, the formula i need is:

rows in detail from 1-4000

if column I = advertising and
if column Q is not empty
then tell me of those in the range, what is the lowest #

also need for highest #