#### Judo Tom

##### Board Regular
hello,

I need help creating a median formula.

i have 2 worksheets on the worksheetA the user enters a list of names. on worksheetB is the data for these names. the function needs to get all the names search to see if they match on worksheet b and then take the values and return an median

this is the function i had used to return the average

=(DAVERAGE('2000'!\$A\$1:\$CR\$10650,\$A3,Inputs!\$B\$9:\$C\$26))

200 is the name of the data table a1:cr10650 encompasses the complete table. A3 is the column refrence, inputs is the table where the names are listed and b9/c26 is that complete table..

=MEDIAN(IF(('1998'!\$A\$2:\$A\$10649=Inputs!\$B\$9:\$B\$26)*('1998'!\$CS\$2:\$CS\$10649=Inputs!\$C\$9:\$C\$26),''1998'!\$B\$2:\$B\$10649))

i was hoping this formula would replace the old one but it didnt work. basically i wanted this formula to see if there was a name in column a on the 1998 worksheet which matched the name on the inputs worksheet and if cs (Another field) matched c on the inputs worksheet and if it did to return b from 1998 and give me the median...

hope this is enough detail.. and not too much either...

This message was edited by Judo Tom on 2002-09-06 11:22

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
TTT

cmon... someone has to have an idea..

1. Part of your example syntax is incorrect and or typos
2. You could have provided a clearer question.
3. I do not know what you intended by your follow up point.

The following is an example of calculation of an overall Median and a calculation of Median by item. It is probably different than your information. It includes

Number Category Price
4 Apples 0.25
5 Oranges 0.5
2 Apples 0.35
7 Plums 0.3
4 Oranges 0.4
2 Plums 0.25
3 Oranges 0.3
2 Oranges 0.2
9 Apples 0.9
1 Oranges 0.1

Plums 0.3
Apples 0.9
Oranges 0.4

Overall 0.35

Formulas for

Overall =MEDIAN(IF(rQty>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(rQty)))),rPrice))

Item such as Plums
=MEDIAN(IF((rItems=B71)*(rQty)>=TRANSPOSE(ROW(INDIRECT("1:"&MAX(rQty)))),rPrice))

Note: both formulas are Array Formulas; enter them with Ctrl-Shift-Enter
This message was edited by Dave Patton on 2002-09-08 02:52

Replies
4
Views
459
Replies
1
Views
678
Replies
8
Views
389
Replies
5
Views
694
Replies
0
Views
259

1,219,960
Messages
6,151,163
Members
451,012
Latest member
OH650R

### 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.

### Which adblocker are you using?

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

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