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