average

mattrx731

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
I'm looking for a formula in C2 through C8.
C2 needs to look at A2 and if that matches a cell in D2:D40 I need it to average the adjacent cell in column E.
So in this example C2 would match D2:D6 and need to find the average of E2:E6 (600,585,80,380,380) C2 should = 405
Thansk

<TABLE borderColor=#329801 cellSpacing=1 cellPadding=2 width=889 border=1><TBODY><TR><TD width="8%" height=17>


</TD><TD width="31%" height=17>A


</TD><TD width="17%" height=17>B


</TD><TD width="8%" height=17>C


</TD><TD width="31%" height=17>D


</TD><TD width="5%" height=17>E


</TD></TR><TR><TD width="8%" height=17>1


</TD><TD width="31%" height=17>MEDICATION (DRUG ID) Unique


</TD><TD width="17%" height=17>Billing Code Unique


</TD><TD width="8%" height=17>Avg Dose


</TD><TD width="31%" height=17>MEDICATION (DRUG ID)


</TD><TD width="5%" height=17>DOSE


</TD></TR><TR><TD width="8%" height=17>2


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="17%" height=17>2502645


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="5%" height=17>600


</TD></TR><TR><TD width="8%" height=17>3


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="17%" height=17>2502760


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="5%" height=17>585


</TD></TR><TR><TD width="8%" height=17>4


</TD><TD width="31%" height=17>BORTEZOMIB 3.5 MG/3.5 ML BU


</TD><TD width="17%" height=17>2502785


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="5%" height=17>80


</TD></TR><TR><TD width="8%" height=17>5


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="17%" height=17>2503490


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="5%" height=17>380


</TD></TR><TR><TD width="8%" height=17>6


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="17%" height=17>2503904


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BEVACIZUMAB 10 MG/0.4 ML MG


</TD><TD width="5%" height=17>380


</TD></TR><TR><TD width="8%" height=17>7


</TD><TD width="31%" height=17>CETUXIMAB 2 MG/ML VIAL


</TD><TD width="17%" height=17>2503905


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>8


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="17%" height=17>2504580


</TD><TD width="8%" height=17>*


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>9


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>10


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>11


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>12


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>13


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>14


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>20


</TD></TR><TR><TD width="8%" height=17>15


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BLEOMYCIN SULFATE 15 UNIT VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>16


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BORTEZOMIB 3.5 MG/3.5 ML BU


</TD><TD width="5%" height=17>2.3


</TD></TR><TR><TD width="8%" height=17>17


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BORTEZOMIB 3.5 MG/3.5 ML BU


</TD><TD width="5%" height=17>2.35


</TD></TR><TR><TD width="8%" height=17>18


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BORTEZOMIB 3.5 MG/3.5 ML BU


</TD><TD width="5%" height=17>2.3


</TD></TR><TR><TD width="8%" height=17>19


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>BORTEZOMIB 3.5 MG/3.5 ML BU


</TD><TD width="5%" height=17>1.2


</TD></TR><TR><TD width="8%" height=17>20


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>420


</TD></TR><TR><TD width="8%" height=17>21


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>418


</TD></TR><TR><TD width="8%" height=17>22


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>425


</TD></TR><TR><TD width="8%" height=17>23


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>665


</TD></TR><TR><TD width="8%" height=17>24


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>334


</TD></TR><TR><TD width="8%" height=17>25


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CARBOPLATIN 50 MG/5 ML VIAL


</TD><TD width="5%" height=17>680


</TD></TR><TR><TD width="8%" height=17>26


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="5%" height=17>425


</TD></TR><TR><TD width="8%" height=17>27


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="5%" height=17>0


</TD></TR><TR><TD width="8%" height=17>28


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="5%" height=17>75


</TD></TR><TR><TD width="8%" height=17>29


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="5%" height=17>425


</TD></TR><TR><TD width="8%" height=17>30


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 10 MG/5 ML VIAL


</TD><TD width="5%" height=17>0


</TD></TR><TR><TD width="8%" height=17>31


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 2 MG/ML VIAL


</TD><TD width="5%" height=17>680


</TD></TR><TR><TD width="8%" height=17>32


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 2 MG/ML VIAL


</TD><TD width="5%" height=17>420


</TD></TR><TR><TD width="8%" height=17>33


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CETUXIMAB 2 MG/ML VIAL


</TD><TD width="5%" height=17>410


</TD></TR><TR><TD width="8%" height=17>34


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>35


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>36


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>37


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>10


</TD></TR><TR><TD width="8%" height=17>38


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>140


</TD></TR><TR><TD width="8%" height=17>39


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 10 MG/10 ML VIAL


</TD><TD width="5%" height=17>52


</TD></TR><TR><TD width="8%" height=17>40


</TD><TD width="31%" height=17>


</TD><TD width="17%" height=17>


</TD><TD width="8%" height=17>


</TD><TD width="31%" height=17>CISPLATIN 50 MG/50 ML VIAL


</TD><TD width="5%" height=17>10


</TD></TR></TBODY></TABLE>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try:

=SUMIF(D2:D40,"="&A2,E2:E40)/COUNTIF(D2:D40,"="&A2)

Or if you use 2007, I believe you can use:

=AVERAGEIF(D2:D40,"="&A2,E2:E40)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA to Categorise based on columns
Replies
3
Views
144
Peter_SSs
Peter_SSs
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
172
sanrama
S
M
  • Question Question
SLA Time Calculation
Replies
5
Views
306
felixstraube
F

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top