|
Mix/Max/Avg Help based on dynamic ranges
WinXP Excel 2k3
Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ....and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ....to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ....etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Assuming you have range named Activity like so:
LocationSize Function ActivityCount 3 Copy 96 33 Copy 54 4 Copy 93 44 Copy 77 3 Print 66 33 Print 15 4 Print 73 44 Print 85 and another range named Location like so (this takes your range of 1-10 and breaks it into two columns, LBound & UBound ) Lbound Ubound 1 10 11 20 21 30 31 40 41 50 then you can use MS Query (Data-Get External Data-New database Query) to generate the answers you want. In MS Query, select Excel, the navigate to and choose the file that has your data in it and, when prompted, select either the Activity or the Location ranges. Click on OK to get to the step in the Wizard that allows you to Edit in MS Query Once the Query editor is open, click on the SQL button and paste in this code SELECT location.Ubound, Activity.Function, Min(Activity.ActivityCount) AS 'Minimum', Max(Activity.ActivityCount) AS 'maximum', Avg(Activity.ActivityCount) AS 'average' FROM Activity Activity, location location WHERE ( Activity.LocationSize=location.lbound And Activity.LocationSize<=location.ubound) GROUP BY location.Ubound, Activity.Function Then click on OK. Under the File menu is an option to return the query's data to Excel After you return it to Excel, change the UBound & LBound numbers in the Location range, then right click the Query results and choose refresh "Hile" wrote: WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Matrix!$B$5:$B$8
HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Biff
I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
I can't break up the headcount range into 2 separate columns w/o undoing all
the calculations based on this column under the x-x format. Biff already tried to help me redo the existing calcs with the 2 range col and it didn't work. -- Hile "Duke Carey" wrote: Assuming you have range named Activity like so: LocationSize Function ActivityCount 3 Copy 96 33 Copy 54 4 Copy 93 44 Copy 77 3 Print 66 33 Print 15 4 Print 73 44 Print 85 and another range named Location like so (this takes your range of 1-10 and breaks it into two columns, LBound & UBound ) Lbound Ubound 1 10 11 20 21 30 31 40 41 50 then you can use MS Query (Data-Get External Data-New database Query) to generate the answers you want. In MS Query, select Excel, the navigate to and choose the file that has your data in it and, when prompted, select either the Activity or the Location ranges. Click on OK to get to the step in the Wizard that allows you to Edit in MS Query Once the Query editor is open, click on the SQL button and paste in this code SELECT location.Ubound, Activity.Function, Min(Activity.ActivityCount) AS 'Minimum', Max(Activity.ActivityCount) AS 'maximum', Avg(Activity.ActivityCount) AS 'average' FROM Activity Activity, location location WHERE ( Activity.LocationSize=location.lbound And Activity.LocationSize<=location.ubound) GROUP BY location.Ubound, Activity.Function Then click on OK. Under the File menu is an option to return the query's data to Excel After you return it to Excel, change the UBound & LBound numbers in the Location range, then right click the Query results and choose refresh "Hile" wrote: WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Did you enter Biff's formula as an array (Ctrl-Shift-Enter)?
MS Query allows you to treat your file as if it were a database. Your named ranges MUST INCLUDE THE COLUMN HEADERS. I didn't make that point in the first message. The query I gave you is dependent on your breaking out the ranges as Biff suggested If you provide the exact column names and the exact range names, I can provide you with the precise SQL code you'd need to use "Hile" wrote: Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
I'll be more than happy to share the file
If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
I did enter as array, but it didn't work.
The source tab (survey response) is not the prettiest of creatures. Everything is text and column headings are a complex mix of merged cells and subcells. It's not a simple tabular table. And I wasn't given enough time to clean it up before arriving at the stats. It would probably be easiest to understand if you can see it. Is there a place I can send it? -- Hile "Duke Carey" wrote: Did you enter Biff's formula as an array (Ctrl-Shift-Enter)? MS Query allows you to treat your file as if it were a database. Your named ranges MUST INCLUDE THE COLUMN HEADERS. I didn't make that point in the first message. The query I gave you is dependent on your breaking out the ranges as Biff suggested If you provide the exact column names and the exact range names, I can provide you with the precise SQL code you'd need to use "Hile" wrote: Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
darn it! The file is 2.1mb
-- Hile "T. Valko" wrote: I'll be more than happy to share the file If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Do you have a file compression utility?
If so, compress the file (I can only open compressed *.zip files) My email chokes on stuff 1mb. -- Biff Microsoft Excel MVP "Hile" wrote in message ... darn it! The file is 2.1mb -- Hile "T. Valko" wrote: I'll be more than happy to share the file If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Yes pkzip
I'll write up a quick explanation of what I need and send over. Thank you so much. -- Hile "T. Valko" wrote: Do you have a file compression utility? If so, compress the file (I can only open compressed *.zip files) My email chokes on stuff 1mb. -- Biff Microsoft Excel MVP "Hile" wrote in message ... darn it! The file is 2.1mb -- Hile "T. Valko" wrote: I'll be more than happy to share the file If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
I can't help you.
Here's what my anti-virus software says about your file: FILE QUARANTINED ---------------- The original contents of Field Printing HC & Analysis 20080821.ZIP have been replaced with this message because of its Exceedingly Infected characteristics. -- Biff Microsoft Excel MVP "Hile" wrote in message ... Yes pkzip I'll write up a quick explanation of what I need and send over. Thank you so much. -- Hile "T. Valko" wrote: Do you have a file compression utility? If so, compress the file (I can only open compressed *.zip files) My email chokes on stuff 1mb. -- Biff Microsoft Excel MVP "Hile" wrote in message ... darn it! The file is 2.1mb -- Hile "T. Valko" wrote: I'll be more than happy to share the file If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
That's interesting.
I just scanned my file with Sophos full client and there's nothing wrong with it. Thanks for trying. -- Hile "T. Valko" wrote: I can't help you. Here's what my anti-virus software says about your file: FILE QUARANTINED ---------------- The original contents of Field Printing HC & Analysis 20080821.ZIP have been replaced with this message because of its Exceedingly Infected characteristics. -- Biff Microsoft Excel MVP "Hile" wrote in message ... Yes pkzip I'll write up a quick explanation of what I need and send over. Thank you so much. -- Hile "T. Valko" wrote: Do you have a file compression utility? If so, compress the file (I can only open compressed *.zip files) My email chokes on stuff 1mb. -- Biff Microsoft Excel MVP "Hile" wrote in message ... darn it! The file is 2.1mb -- Hile "T. Valko" wrote: I'll be more than happy to share the file If the file is <1mb you can send it to me. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Include a *detailed* explanation of what you're trying to do! -- Biff Microsoft Excel MVP "Hile" wrote in message ... Biff I added the column and broke up the ranges but the calculation did not yield results. It returned #NA. I already have 8 calcs based on this methodology and can't afford to redo all this work. Duke's answer seems pretty complicated but I'll give it a try. I've never used the MS Query function. -- Hile "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Hello, Biff,
I am trying to accomplish a MAX formula for 3 specified ranges within 1 column and was able to adapt a portion of the following from your reply below: =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) I have 3 ranges within a column - 0-999, 1000-1999, 2000+ and I am looking to find the MAX number in each range. I have the formulas for the first and last range, but I am having trouble getting the formula to work for the middle range. I modifed the formula to =MAX(sumproduct($B$2:$B$1344=1000)*($B$2:$B$1344< 2000))) and it gives me a result of 79. I have tried entering like this and as an array and get the same result. It works on the first range when I change 1000 to 0 and 2000 to 1000, but not the mid-range. Can you help? Thanks, Valerie "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Try these array formulas** :
For 0-999 =MAX(IF($B$2:$B$1344<=999,$B$2:$B$1344)) For 1000-1999 =MAX(IF(($B$2:$B$1344=1000)*($B$2:$B$1344<=1999), $B$2:$B$1344)) For 2000+ =MAX(IF($B$2:$B$1344=2000,$B$2:$B$1344)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Valerie" wrote in message ... Hello, Biff, I am trying to accomplish a MAX formula for 3 specified ranges within 1 column and was able to adapt a portion of the following from your reply below: =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) I have 3 ranges within a column - 0-999, 1000-1999, 2000+ and I am looking to find the MAX number in each range. I have the formulas for the first and last range, but I am having trouble getting the formula to work for the middle range. I modifed the formula to =MAX(sumproduct($B$2:$B$1344=1000)*($B$2:$B$1344< 2000))) and it gives me a result of 79. I have tried entering like this and as an array and get the same result. It works on the first range when I change 1000 to 0 and 2000 to 1000, but not the mid-range. Can you help? Thanks, Valerie "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
Thanks, Biff!! Works like a champ!
"T. Valko" wrote: Try these array formulas** : For 0-999 =MAX(IF($B$2:$B$1344<=999,$B$2:$B$1344)) For 1000-1999 =MAX(IF(($B$2:$B$1344=1000)*($B$2:$B$1344<=1999), $B$2:$B$1344)) For 2000+ =MAX(IF($B$2:$B$1344=2000,$B$2:$B$1344)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Valerie" wrote in message ... Hello, Biff, I am trying to accomplish a MAX formula for 3 specified ranges within 1 column and was able to adapt a portion of the following from your reply below: =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) I have 3 ranges within a column - 0-999, 1000-1999, 2000+ and I am looking to find the MAX number in each range. I have the formulas for the first and last range, but I am having trouble getting the formula to work for the middle range. I modifed the formula to =MAX(sumproduct($B$2:$B$1344=1000)*($B$2:$B$1344< 2000))) and it gives me a result of 79. I have tried entering like this and as an array and get the same result. It works on the first range when I change 1000 to 0 and 2000 to 1000, but not the mid-range. Can you help? Thanks, Valerie "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
Mix/Max/Avg Help based on dynamic ranges
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Valerie" wrote in message ... Thanks, Biff!! Works like a champ! "T. Valko" wrote: Try these array formulas** : For 0-999 =MAX(IF($B$2:$B$1344<=999,$B$2:$B$1344)) For 1000-1999 =MAX(IF(($B$2:$B$1344=1000)*($B$2:$B$1344<=1999), $B$2:$B$1344)) For 2000+ =MAX(IF($B$2:$B$1344=2000,$B$2:$B$1344)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Valerie" wrote in message ... Hello, Biff, I am trying to accomplish a MAX formula for 3 specified ranges within 1 column and was able to adapt a portion of the following from your reply below: =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) I have 3 ranges within a column - 0-999, 1000-1999, 2000+ and I am looking to find the MAX number in each range. I have the formulas for the first and last range, but I am having trouble getting the formula to work for the middle range. I modifed the formula to =MAX(sumproduct($B$2:$B$1344=1000)*($B$2:$B$1344< 2000))) and it gives me a result of 79. I have tried entering like this and as an array and get the same result. It works on the first range when I change 1000 to 0 and 2000 to 1000, but not the mid-range. Can you help? Thanks, Valerie "T. Valko" wrote: Matrix!$B$5:$B$8 HC Range 1-10 11-30 31-50 50- I would recommend that you split those into separate cells. Your formulas would then be less complicated. For your last range I'd use a really big arbitrary number that you know will never be exceded. Like this: ...........B..........C 5........1..........10 6.......11.........30 7.......31.........50 8.......51.........1000 Then this: =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) Becomes this: =SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5)) Now your avg, min, max become relatively easy. Follow this general syntax and array enter** : =AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums)) Where Nums are the values to avg based on the head count range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Hile" wrote in message ... WinXP Excel 2k3 Ok...this will be long so thank you in advance for reading: I have a spreadsheet with survey results (Analysis!) about users' device utilization (print/scan/fax/copy) imported all as text from web app. Linked to headcount data for locn sizes based on location# in survey via this formula in column range $I$7:$I$151... =VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE ) ...and now want to derive some stats from the answers in a summary tab (Matrix!). Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is deemed optimal for building device profiles based on locn size and device volume, and so formulas need to recalc each time ranges change. HC Range 1-10 11-30 31-50 50- All the other calcs on Matrix! are based on HC Range col, because they go fetch the info in Analysis! based on whether or not that locn fits the range size on this column. This is the dynamic range part. Bernie D. was kind enough to help with counting the # of locns which fit the range on each cell; I've then been able to modify his formula in Matrix!$G5:$G8... =COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))) ...to give me average # of devices per device type by replacing countif with sumif function and dividing by the results of the countif formula... =(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5 I thought I would be able to use this methodology throughout but...Now I'm trying to figure out device volumes. Problem is, I've hit a wall trying to use the min, max and avg within the range condition. The Analysis! tab needs to also be free to be sorted and resorted for other purposes w/o affecting the results of the Matrix!. So I want each formula to give me the Min, Max and Avg of all records in Analysis!$7:$151 that fall headcountwise within the range specified, AND I need each formula to ignore zero and blank cells. Volumes are located as follows: Print Volume: Analysis!$AH$7:$AH$151 Copy Volume: Analysis!$CQ$7:$CQAH$151 ...etc, I can plug in the others once I have a working formula I hope I gave enough info and didn't confuse things. I'll be more than happy to share the file if I'm told where to send it. I've been working on it for 2 days now. I tried this syntax which in my feeble mind is what I want but excel won't accept it, it shades [$I$151"="] as an error =min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151)) Hopefully, whatever I get from this post I can also use for MAX and AVERAGE functions. Thank you all who managed to read the whole thing before falling asleep. :-) When you wake up, I would LUUUUUV some help! -- Hile |
| All times are GMT +1. The time now is 12:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com