Date Range

K

Karen Smith

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 #
 
K

Karen Smith

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?
 
P

Pete_UK

Try this array* formula:

=MAX(IF((I1:I4000="advertising")*(Q1:Q4000>0),P1:p4000,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:Q4000>0),P1:p4000,100000000))

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

Hope this helps.

Pete
 
Top