You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Valerie" wrote in message
...
Thanks, Biff!! Works like a champ!

"T. Valko" wrote:

Try these array formulas** :

For 0-999

=MAX(IF($B$2:$B$1344<=999,$B$2:$B$1344))

For 1000-1999

=MAX(IF(($B$2:$B$1344=1000)*($B$2:$B$1344<=1999), $B$2:$B$1344))

For 2000+

=MAX(IF($B$2:$B$1344=2000,$B$2:$B$1344))

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

--
Biff
Microsoft Excel MVP


"Valerie" wrote in message
...
Hello, Biff,
I am trying to accomplish a MAX formula for 3 specified ranges within 1
column and was able to adapt a portion of the following from your reply
below:

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

I have 3 ranges within a column - 0-999, 1000-1999, 2000+ and I am
looking
to find the MAX number in each range. I have the formulas for the
first
and
last range, but I am having trouble getting the formula to work for the
middle range.

I modifed the formula to
=MAX(sumproduct($B$2:$B$1344=1000)*($B$2:$B$1344< 2000))) and it gives
me
a
result of 79. I have tried entering like this and as an array and get
the
same result. It works on the first range when I change 1000 to 0 and
2000
to
1000, but not the mid-range. Can you help?

Thanks,
Valerie

"T. Valko" wrote:

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

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

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

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

Then this:

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

Becomes this:

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

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

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

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

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


--
Biff
Microsoft Excel MVP


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

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

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

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

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

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

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

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

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

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

--
Hile