Mix/Max/Avg Help based on dynamic ranges

Hile · Aug 26, 2008

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Duke Carey · Aug 26, 2008

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 said:
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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

T. Valko · Aug 26, 2008

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(Matrix!$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)*(Analysis!$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 said:
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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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 · Aug 26, 2008

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 said:
Matrix!$B$5:$B$8
HC Range
1-10
11-30
31-50
50-

Click to expand...

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(Matrix!$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)*(Analysis!$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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Hile · Aug 26, 2008

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 said:
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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Duke Carey · Aug 26, 2008

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 said:
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 said:

Matrix!$B$5:$B$8
HC Range
1-10
11-30
31-50
50-

Click to expand...

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(Matrix!$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)*(Analysis!$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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

T. Valko · Aug 26, 2008

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 said:
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 said:

Matrix!$B$5:$B$8
HC Range
1-10
11-30
31-50
50-

Click to expand...

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(Matrix!$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)*(Analysis!$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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Hile · Aug 26, 2008

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 said:
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 said:

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 said:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Hile · Aug 26, 2008

darn it! The file is 2.1mb
--
Hile

T. Valko said:
I'll be more than happy to share the file

Click to expand...

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 said:

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 said:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

T. Valko · Aug 26, 2008

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 said:
darn it! The file is 2.1mb
--
Hile

T. Valko said:

I'll be more than happy to share the file

Click to expand...

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 said:

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

:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Hile · Aug 26, 2008

Yes pkzip
I'll write up a quick explanation of what I need and send over. Thank you so
much.

--
Hile

T. Valko said:
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 said:

darn it! The file is 2.1mb
--
Hile

T. Valko said:

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

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

:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

T. Valko · Aug 26, 2008

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 said:
Yes pkzip
I'll write up a quick explanation of what I need and send over. Thank you
so
much.

--
Hile

T. Valko said:

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 said:

darn it! The file is 2.1mb
--
Hile

:

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

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

:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Hile · Aug 27, 2008

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 said:
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 said:

Yes pkzip
I'll write up a quick explanation of what I need and send over. Thank you
so
much.

--
Hile

T. Valko said:

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

darn it! The file is 2.1mb
--
Hile

:

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

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

:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Valerie · Sep 8, 2008

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)*(Analysis!$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 said:
Matrix!$B$5:$B$8
HC Range
1-10
11-30
31-50
50-

Click to expand...

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(Matrix!$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)*(Analysis!$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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

T. Valko · Sep 8, 2008

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 said:
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)*(Analysis!$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 said:

Matrix!$B$5:$B$8
HC Range
1-10
11-30
31-50
50-

Click to expand...

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(Matrix!$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)*(Analysis!$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 said:

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Valerie · Sep 9, 2008

Thanks, Biff!! Works like a champ!

T. Valko said:
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 said:

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)*(Analysis!$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 said:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

T. Valko · Sep 9, 2008

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Valerie said:
Thanks, Biff!! Works like a champ!

T. Valko said:

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 said:

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)*(Analysis!$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

:

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(Matrix!$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)*(Analysis!$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

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(Matrix!$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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,">"&VALUE(MID(Matrix!$B5,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(Matrix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151">"&VALUE(MID(Matrix!$B5,FIND("-",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!

Click to expand...

Click to expand...

Help with Nested Range counts	3	Aug 21, 2008
Finding MIN across worksheets while excluding blanks	2	Nov 23, 2008
vlookup from variable matrix	7	Dec 2, 2003
How to find max value in a range then give value in cell 2 rows above that max	1	Dec 8, 2011
Max with criteria	10	Jul 24, 2003
Excel2000: Data validation using values returned by named ranges	4	May 7, 2004
Trying to find additional ranges outside of min, max, and average	2	Aug 17, 2013
How can I use a sheet in a formula based on the value in a cell	2	Nov 13, 2007

Mix/Max/Avg Help based on dynamic ranges

Hile

Duke Carey

T. Valko

Hile

Hile

Duke Carey

T. Valko

Hile

Hile

T. Valko

Hile

T. Valko

Hile

Valerie

T. Valko

Valerie

T. Valko

Ask a Question

Similar Threads