Need to average division of two columns based on criteria

[Guest]

I have two sheets
Sheet 1 contains Catagories with respective beginning and ending values (Ie
Category A begins at 10 and ends at 20) There are about 75 categories all
with different beginning and ending values. There is no overlapping, so no
category can contain the same number.
Category CPT Range Start End
E&M Office Visits 99201 - 99215 99201 99215
E&M Hospital Services 99217 - 99239 99217 99239
E&M Consultations 99241 - 99255 99241 99255
E&M Neonatal 99295 - 99300 99295 99300

Sheet 2 contains all the numbers that correspond to the 75 categories (Ie 10
- 10000) in column A. Column B contains an "amount" for each number and
column C contains another "amount" for each number.

Column A Column B Column C
99201 35 40
99202 40 45
99203 45 50
99204 50 55

What i am trying to do is return the average of the range for a category
when dividing one amount by another amount. So for Category E&M Office
Visits i would want to return the average of column c/column b for all codes
within the range (99201-99215). Can this be done without using a macro??

For further information (Column A in Sheet 2 Contains 16,0000 numbers
ranging from 10000 to 99999)

 

[Guest]

If I understand you correctly then the answer is yes. You can do this using
an array expression. For the sake of simplicity in providing an expression,
I'll assume that both tables are on the same sheet (but this should work for
separate sheets as well). Say that your first table is at A1:C4 and your
second table is at G1:I4. For the first Category (in row 1), you can get the
average of the division of the numbers in columns H and I for items in G that
fall in the range between B and C using:

=SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($I$1:$I$4))/SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($H$1:$H$4))

ONCE YOU TYPE in the expression, instead of simply hitting Enter, hit
Ctrl-Shift-Enter. This will make the expression into an array expression
(and "{}"brackets will automatically be added to it).

You can then copy the expression down for each of the categories.

Hope this helps.

Will

 

[Guest]

That seems to do the trick, thank you.

If I understand you correctly then the answer is yes. You can do this using
an array expression. For the sake of simplicity in providing an expression,
I'll assume that both tables are on the same sheet (but this should work for
separate sheets as well). Say that your first table is at A1:C4 and your
second table is at G1:I4. For the first Category (in row 1), you can get the
average of the division of the numbers in columns H and I for items in G that
fall in the range between B and C using:

=SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($I$1:$I$4))/SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($H$1:$H$4))

ONCE YOU TYPE in the expression, instead of simply hitting Enter, hit
Ctrl-Shift-Enter. This will make the expression into an array expression
(and "{}"brackets will automatically be added to it).

You can then copy the expression down for each of the categories.

Hope this helps.

Will

 

[Guest]

Now that i got the basic formula figured out, how do i tell it to ignore
errors??

 

[Guest]

I assume the error you are getting is a divide by 0. There must be
categories that don't have non-zero data. You can eliminate this by adding a
check for 0 denominator values. The formula previously provided would become:

=IF(SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($H$1:$H$4))=0,"put here whatever
you want the cell to read if there is an
error",SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($I$1:$I$4))/SUM(($G$1:$G$4>=B1)*($G$1:$G$4<=C1)*($H$1:$H$4)))

Will