H

#### Hile

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!