Dynamic Data Range
 

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.
--
thanks, mc

Dynamic Data Range
 

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

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

--

HTH

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


"dallin" wrote in message
...
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.
--
thanks, mc

Dynamic Data Range
 

dallin Wrote:
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.
--
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=486950

Dynamic Data Range
 

perfect thanks.
--
thanks, mc


"Bob Phillips" wrote:

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

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

--

HTH

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


"dallin" wrote in message
...
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.
--
thanks, mc

Dynamic Data Range
 

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


"BenjieLop" wrote:


dallin Wrote:
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.
--
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=486950