Re: Min Function Excluding Zero Values & More
- From: "Biff" <biffinpitt@xxxxxxxxxxx>
- Date: Sat, 31 Dec 2005 22:40:01 -0500
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" <WeatherGuy.20xpby_1136082601.7308@xxxxxxxxxxxxxxxxxxxxx> wrote
in message news:WeatherGuy.20xpby_1136082601.7308@xxxxxxxxxxxxxxxxxxxxxxxx
>
> 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?action=getinfo&userid=9254
> View this thread: http://www.excelforum.com/showthread.php?threadid=497216
>
.
- Follow-Ups:
- References:
- Min Function Excluding Zero Values & More
- From: WeatherGuy
- Min Function Excluding Zero Values & More
- Prev by Date: Min Function Excluding Zero Values & More
- Next by Date: Re: Min Function Excluding Zero Values & More
- Previous by thread: Min Function Excluding Zero Values & More
- Next by thread: Re: Min Function Excluding Zero Values & More
- Index(es):
Relevant Pages
|
