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