Hile,
Try replacing the final
Area!$G$5:$G$787
with
IF(Area!$G$5:$G$787>0,Area!$G$5:$G$787)
That should deal with both blanks and zero values.
HTH,
Bernie
MS Excel MVP
"Hile" <(E-Mail Removed)> wrote in message
news:0C65E93D-D00A-4C9E-9C8D-(E-Mail Removed)...
> You are an absolute genius!!!! Thank you so much.
>
> Can I get the MIN and maybe the AVERAGE but definately the MIN to ignore
> zeros and blanks. These are outliers because I don't know if the volume is
> truly zero or if they just didn't answer. It would give me a truer picture
> if
> I take those out.
>
> I already have a separate column counting the # of zeros within each
> range.
> --
> Hile
>
>
> "Bernie Deitrick" wrote:
>
>> Hile,
>>
>> This will pull the MAX from column G for the range given in cell B3:
>>
>> =MAX(IF((Area!$F$5:$F$787>VALUE(LEFT(Matrix!B3,FIND("-",Matrix!B3)-1)))*(Area!$F$5:$F$787<=VALUE(MID(Matrix!B3,FIND("-",Matrix!B3)+1,LEN(Matrix!B3)))),Area!$G$5:$G$787))
>>
>> This will pull the MIN from column G for the range given in cell B3:
>> =MIN(IF((Area!$F$5:$F$787>VALUE(LEFT(Matrix!B3,FIND("-",Matrix!B3)-1)))*(Area!$F$5:$F$787<=VALUE(MID(Matrix!B3,FIND("-",Matrix!B3)+1,LEN(Matrix!B3)))),Area!$G$5:$G$787))
>>
>> This will pull the AVERAGE from column G for the range given in cell B3:
>> =AVERAGE(IF((Area!$F$5:$F$787>VALUE(LEFT(Matrix!B3,FIND("-",Matrix!B3)-1)))*(Area!$F$5:$F$787<=VALUE(MID(Matrix!B3,FIND("-",Matrix!B3)+1,LEN(Matrix!B3)))),Area!$G$5:$G$787))
>>
>> Again, all three are array formulas, entered using Ctrl-Shift-Enter
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Hile" <(E-Mail Removed)> wrote in message
>> news:4214DB0F-7720-45CC-AC7A-(E-Mail Removed)...
>> >I sure hope this works!
>> >
>> > You helped me in this post:
>> > Help with Nested Range counts -
>> > microsoft.public.excel.worksheet.functions
>> > posted 8/21/08
>> >
>> > Can you see if you can help me in this post:
>> > Mix/Max/Avg Help based on dynamic ranges -
>> > microsoft.public.excel.worksheet.functions posted 8/26/08
>> >
>> > I'm desparate, I can't figure out the syntax and can't figure out why
>> > sumproduct is not working either, even though I tried what was already
>> > posted. I've already determined I can't fix it, so it doesn't hurt to
>> > post
>> > this. My project is at a complete halt until I can get this working OR
>> > will
>> > have to analyze the data in a less automated fahsion which is just as
>> > bad.
>> >
>> > --
>> > Hile
>>
>>
>>
|
Similar Threads
