| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
| Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
| Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Linear Mode
