Dynamic Data Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dallin - 21 Nov 2005 18:48 GMT

I have a range of data example:
Jan 100
Feb 200
Mar 300
Apr 400
May 0
Jun 0
July 0
Aug 0
etc...
I am using the min() function on the complete data range and of course it
returns a 0 for the answer. However, I would like the min() function to only
include the Jan - Apr data in the calculation and return 100. How do I
exclude the 0 data because I plan on running the data monthly and picking up
an extra month of data and then would want the formula to recognize the new
months data and again exclude the zeros. Thanks.

Signature

thanks, mc

Reply to this Message

Bob Phillips - 21 Nov 2005 19:00 GMT

=MIN(IF(B1:B100<>0,B1:B100))

which is an array formula, so commit with Ctrl-Shift-Enter.

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> I have a range of data example:
> Jan 100
[quoted text clipped - 12 lines]
> an extra month of data and then would want the formula to recognize the new
> months data and again exclude the zeros. Thanks.

Reply to this Message

dallin - 21 Nov 2005 19:21 GMT

perfect thanks.

Signature

thanks, mc

> =MIN(IF(B1:B100<>0,B1:B100))
>
[quoted text clipped - 19 lines]
> new
> > months data and again exclude the zeros. Thanks.

Reply to this Message

BenjieLop - 21 Nov 2005 19:08 GMT

dallin Wrote:

> I have a range of data example:
> Jan 100
[quoted text clipped - 19 lines]
> --
> thanks, mc

Assuming that entries in Column B are in B2:B13, your formula is

=LARGE(B2:B13,COUNTIF(B2:B13,\">0\")

.. hope this is what you need.

Regards

--
BenjieLo

Reply to this Message

dallin - 21 Nov 2005 19:21 GMT

a number of different ways to skin the cat. Perfect, thanks.

Signature

thanks, mc

> dallin Wrote:
> > I have a range of data example:
[quoted text clipped - 28 lines]
>
> Regards.

Reply to this Message