Min Function Excluding Zero Values & More

[WeatherGuy]

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

[Biff]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Biff]

P.S.

Then you can just use these functions as you normally would:

=MIN(A1:A10)
=MAX(A1:A10)
=AVERAGE(A1:A10)

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[rsenn]

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))

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> P.S.
>
> Then you can just use these functions as you normally would:
>
> =MIN(A1:A10)
> =MAX(A1:A10)
> =AVERAGE(A1:A10)
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[WeatherGuy]

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]

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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> P.S.
>>
>> Then you can just use these functions as you normally would:
>>
>> =MIN(A1:A10)
>> =MAX(A1:A10)
>> =AVERAGE(A1:A10)
>>
>> Biff
>>
>> "Biff" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]>
>>> wrote in message
>>> news:[email protected]...
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>