Formula Guidance Requested

[David Lipetz]

Folks,

I'm baffled as to which formulas to use to perform these calculations:

I have a sheet with three columns:
A = Customer Number
B = Number of Users
C = Total Monthly Fee
There are 700+ rows of data.

The values in A will range from 1-500.

I've created a table on another sheet that segments the above data into
groups that I assign. The table looks like this:

B = Group (Alpha identifier for group)
C = starting number in range (ex. 1)
E = ending number in range (ex. 3)
This table has 9 rows (6:14) with each row representing a different range
(ex. 1-3, 4-6, 7-10, 11-25, etc.)

F = the array formula below which counts the number of customers in the
first sheet that have a user count within the specified range.

=COUNTIF(SHEET1!$B$3:$B$702,">="&C6:C14)-COUNTIF(SHEET1!$B$3:$B$702,">"&E6:E14)
This formula works great.

However, I want to be able to determine average, min, and max of the data in
sheet 1 (column C) for the user count range specified in the table I
created.

How do I go about calculating this?

Thanks in advance.

David

 

[Bob Phillips]

MAX is

=MAX(IF((Sheet1!$B$3:$B$702>=C6)*(Sheet1!$B$3:$B$702<=E6),Sheet1!$C$3:$C$702
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

assuming that I am counting and testing the correct data.

Average and Min work the same principle.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)