Do you have a file compression utility?

If so, compress the file (I can only open compressed *.zip files)

My email chokes on stuff 1mb.

--
Biff
Microsoft Excel MVP


"Hile" wrote in message
...
darn it! The file is 2.1mb
--
Hile


"T. Valko" wrote:

I'll be more than happy to share the file

If the file is <1mb you can send it to me. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Include a *detailed* explanation of what you're trying to do!

--
Biff
Microsoft Excel MVP


"Hile" wrote in message
...
Biff
I added the column and broke up the ranges but the calculation did not
yield
results. It returned #NA. I already have 8 calcs based on this
methodology
and can't afford to redo all this work.

Duke's answer seems pretty complicated but I'll give it a try. I've
never
used the MS Query function.

--
Hile


"T. Valko" wrote:

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

I would recommend that you split those into separate cells. Your
formulas
would then be less complicated.

For your last range I'd use a really big arbitrary number that you
know
will
never be exceded. Like this:

...........B..........C
5........1..........10
6.......11.........30
7.......31.........50
8.......51.........1000

Then this:

=COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))))

Becomes this:

=SUMPRODUCT(--(Analysis!$I$7:$I$151=Matrix!$B5),--(Analysis!$I$7:$I$151<=Matrix!$C5))

Now your avg, min, max become relatively easy. Follow this general
syntax
and array enter** :

=AVERAGE(IF((Analysis!$I$7:$I$151=Matrix!$B5)*(An alysis!$I$7:$I$151<=Matrix!$C5)*(Nums<0),Nums))

Where Nums are the values to avg based on the head count range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Hile" wrote in message
...
WinXP Excel 2k3

Ok...this will be long so thank you in advance for reading:

I have a spreadsheet with survey results (Analysis!) about users'
device
utilization (print/scan/fax/copy) imported all as text from web app.

Linked to headcount data for locn sizes based on location# in survey
via
this formula in column range $I$7:$I$151...
=VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE )
...and now want to derive some stats from the answers in a summary
tab
(Matrix!).

Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set
range
is
deemed optimal for building device profiles based on locn size and
device
volume, and so formulas need to recalc each time ranges change.
HC Range
1-10
11-30
31-50
50-

All the other calcs on Matrix! are based on HC Range col, because
they
go
fetch the info in Analysis! based on whether or not that locn fits
the
range
size on this column. This is the dynamic range part. Bernie D. was
kind
enough to help with counting the # of locns which fit the range on
each
cell;
I've then been able to modify his formula in Matrix!$G5:$G8...
=COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))))
...to give me average # of devices per device type by replacing
countif
with
sumif function and dividing by the results of the countif formula...
=(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5

I thought I would be able to use this methodology throughout
but...Now
I'm
trying to figure out device volumes. Problem is, I've hit a wall
trying
to
use the min, max and avg within the range condition. The Analysis!
tab
needs
to also be free to be sorted and resorted for other purposes w/o
affecting
the results of the Matrix!. So I want each formula to give me the
Min,
Max
and Avg of all records in Analysis!$7:$151 that fall headcountwise
within
the
range specified, AND I need each formula to ignore zero and blank
cells.
Volumes are located as follows:
Print Volume: Analysis!$AH$7:$AH$151
Copy Volume: Analysis!$CQ$7:$CQAH$151
...etc, I can plug in the others once I have a working formula

I hope I gave enough info and didn't confuse things. I'll be more
than
happy
to share the file if I'm told where to send it. I've been working on
it
for 2
days now. I tried this syntax which in my feeble mind is what I want
but
excel won't accept it, it shades [$I$151"="] as an error
=min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151))

Hopefully, whatever I get from this post I can also use for MAX and
AVERAGE
functions.

Thank you all who managed to read the whole thing before falling
asleep.
:-)
When you wake up, I would LUUUUUV some help!

--
Hile