Calling Bernie Deitrick
 

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

Calling Bernie Deitrick
 

Hile,

This will pull the MAX from column G for the range given in cell B3:

=MAX(IF((Area!$F$5:$F$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(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" wrote in message
...
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

Calling Bernie Deitrick
 

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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(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" wrote in message
...
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

Calling Bernie Deitrick
 

Hile,

Try replacing the final

Area!$G$5:$G$787

with

IF(Area!$G$5:$G$7870,Area!$G$5:$G$787)

That should deal with both blanks and zero values.

HTH,
Bernie
MS Excel MVP

"Hile" wrote in message
...
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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(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" wrote in message
...
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

Calling Bernie Deitrick
 

Super. You ROCK!
--
Hile


"Bernie Deitrick" wrote:

Hile,

Try replacing the final

Area!$G$5:$G$787

with

IF(Area!$G$5:$G$7870,Area!$G$5:$G$787)

That should deal with both blanks and zero values.

HTH,
Bernie
MS Excel MVP

"Hile" wrote in message
...
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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(LEFT(Matrix!B3,FIN D("-",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$787VALUE(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" wrote in message
...
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