| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA Question - Bernie Deitrick helped me previously | Excel Discussion (Misc queries) | |||
| PING: Bernie Deitrick - Calloway Golf Scoring | Excel Discussion (Misc queries) | |||
| Bernie Deitrick | Excel Discussion (Misc queries) | |||
| My New Macro from Bernie | Excel Worksheet Functions | |||
| Bernie Deitrick | Excel Worksheet Functions | |||
Linear Mode
