| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Function Excluding Zero Values & More
Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do. I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far. I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve: 1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part. 2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense? The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet. Any help would be appreciated. Thanks, --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
Hi!
How does this sound: Since you only want Min, Max, Avg on the Sum of full sets of cells, B:M In A1 try this formula: =IF(COUNT(B1:M1)<12,"",SUM(B1:M1)) Copy down to A10. So, what will happen is that your Sum formula will return a blank until all the cells in the range B1:M1 have numbers in them. Instead of returning 0 and having to use a formula that excludes it, this formula returns an empty text string that will be ignored by the Min, Max and Avg functions. Biff "WeatherGuy" wrote in message ... Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do. I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far. I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve: 1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part. 2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense? The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet. Any help would be appreciated. Thanks, --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
P.S.
Then you can just use these functions as you normally would: =MIN(A1:A10) =MAX(A1:A10) =AVERAGE(A1:A10) Biff "Biff" wrote in message ... Hi! How does this sound: Since you only want Min, Max, Avg on the Sum of full sets of cells, B:M In A1 try this formula: =IF(COUNT(B1:M1)<12,"",SUM(B1:M1)) Copy down to A10. So, what will happen is that your Sum formula will return a blank until all the cells in the range B1:M1 have numbers in them. Instead of returning 0 and having to use a formula that excludes it, this formula returns an empty text string that will be ignored by the Min, Max and Avg functions. Biff "WeatherGuy" wrote in message ... Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do. I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far. I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve: 1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part. 2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense? The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet. Any help would be appreciated. Thanks, --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
It will take two cells, but here is how to do it. In a helper cell, E18, type this formula. ="A"&COUNTIF(A:A,"<0")-1 Then in the cell where you want your answer, type =MIN(A1:INDIRECT(E18)) -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
P.S.S.
Then you can just use these functions as you normally would: =AVERAGE(A1:A10) Well, you'd have to make sure there is at least 1 numeric value in the range A1:A10 or you'll get a #DIV/0! error with the Avg function. So, something like this: =IF(COUNT(A1:A10),AVERAGE(A1:A10),"") Biff "Biff" wrote in message ... P.S. Then you can just use these functions as you normally would: =MIN(A1:A10) =MAX(A1:A10) =AVERAGE(A1:A10) Biff "Biff" wrote in message ... Hi! How does this sound: Since you only want Min, Max, Avg on the Sum of full sets of cells, B:M In A1 try this formula: =IF(COUNT(B1:M1)<12,"",SUM(B1:M1)) Copy down to A10. So, what will happen is that your Sum formula will return a blank until all the cells in the range B1:M1 have numbers in them. Instead of returning 0 and having to use a formula that excludes it, this formula returns an empty text string that will be ignored by the Min, Max and Avg functions. Biff "WeatherGuy" wrote in message ... Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do. I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far. I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve: 1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part. 2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense? The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet. Any help would be appreciated. Thanks, --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
What happens if the result is 1 or 2?
COUNTIF(A:A,"<0") If the result is 1: =MIN(A1:INDIRECT(E18)) =MIN(A1:A0) If the result is 2: =MIN(A1:A1) Biff "rsenn" wrote in message ... It will take two cells, but here is how to do it. In a helper cell, E18, type this formula. ="A"&COUNTIF(A:A,"<0")-1 Then in the cell where you want your answer, type =MIN(A1:INDIRECT(E18)) -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
Biff, Thank you for the reply. I see what you're getting at and that would work, however, I do need a "running" total on the rows before the data entry is complete. --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
Ok, one last P.S. <geez
I'm assuming that the numbers in B:M aren't such that: -10, 10 Where that sum would be 0. Biff "Biff" wrote in message ... P.S.S. Then you can just use these functions as you normally would: =AVERAGE(A1:A10) Well, you'd have to make sure there is at least 1 numeric value in the range A1:A10 or you'll get a #DIV/0! error with the Avg function. So, something like this: =IF(COUNT(A1:A10),AVERAGE(A1:A10),"") Biff "Biff" wrote in message ... P.S. Then you can just use these functions as you normally would: =MIN(A1:A10) =MAX(A1:A10) =AVERAGE(A1:A10) Biff "Biff" wrote in message ... Hi! How does this sound: Since you only want Min, Max, Avg on the Sum of full sets of cells, B:M In A1 try this formula: =IF(COUNT(B1:M1)<12,"",SUM(B1:M1)) Copy down to A10. So, what will happen is that your Sum formula will return a blank until all the cells in the range B1:M1 have numbers in them. Instead of returning 0 and having to use a formula that excludes it, this formula returns an empty text string that will be ignored by the Min, Max and Avg functions. Biff "WeatherGuy" wrote in message ... Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do. I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far. I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve: 1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part. 2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense? The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet. Any help would be appreciated. Thanks, --Robert-- -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Min Function Excluding Zero Values & More
RSENN, Thank you for your reply. I had to modify it slightly because my actual range is B8:B23 (not A1:A10) but I was able to tweak it and it works perfectly. This one function will really help me out with several items I'm working on with weather data analysis. Thank you very much! --Robert -- WeatherGuy ------------------------------------------------------------------------ WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254 View this thread: http://www.excelforum.com/showthread...hreadid=497216 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
| look up function and sum values | Excel Worksheet Functions | |||
| Selecting a range of values for another function | Excel Worksheet Functions | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| how do I insert a function that chooses between two text values? | Excel Worksheet Functions | |||
Linear Mode
