Calling Bernie Deitrick

[Hile]

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.

 

[Bernie Deitrick]

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]

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.

 

[Bernie Deitrick]

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]

Super. You ROCK!
--
Hile

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