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