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

 

[Biff]

P.S.

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

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

Biff

 

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

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

 

[WeatherGuy]

Biff,

Thank you for the reply. I see what you're getting at and that woul
work, however, I do need a "running" total on the rows before the dat
entry is complete.

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

 

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