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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
152
dilshod_k
D
M
  • Solved
Automating episode numbers when date changes
Replies
6
Views
102
Keebler
Keebler
M
  • Question
TEXTAFTER with textbefore...to extract number or series of numbers.
2
Replies
12
Views
401
mad3
M
R
  • Question
Reference form of the index function in Microsoft Excel
Replies
1
Views
52
Fluff
Fluff
F
  • Solved
Listing All corresponding dates for a specific value
Replies
3
Views
183
Fluff
Fluff

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

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