Median for Year

  • Thread starter Thread starter KevinAGrogan
  • Start date Start date
K

KevinAGrogan

How do I set up a formula that will calculate the job that produced
the median amount of revenue for each year? (Jobs are counted in the
year they were submitted.) My data is set up as below:

Columns:
A: Customer Name
B: Customer Number
C: Date Job was Submitted
D: Date Job was Returned
E: Turnaround Time
F: Associate who Completed the Job
G: Revenue from Job


Thanks in advance for your help,

Kevin
 
try
to get the row
try
=MATCH(MEDIAN(IF(year(C:C)=2007,G:G)),G:G*(year(C:C)=2007),0)
this will select the first job with the median.
you can use index match to extract the specific info you want
if there are several the job gets a little harder.
 
If there is an even number of data points the median will never (?) be one
of those data points.
 
If there is an even number of data points the median will never (?) be one
of those data points.
Click to expand...

Unless all the data points are the same but in a practical application when
is that ever the case?
 
Ok said:
If there is an even number of data points the median will never (?) be one
of those data points.
Click to expand...

Let me rephrase that:

If there is an even number of data points the median will most likely not be
one of those data points.

** until my next answer
 
Let me rephrase that:

If there is an even number of data points the median will most likely not be
one of those data points.

** until my next answer
Click to expand...

The median will only be one of the data points if:

1)There is an odd number of data points.

OR

2)There is an even number of data points and the middle two are the
same.
 
Tim Shnell said:
The median will only be one of the data points if:

1)There is an odd number of data points.

OR

2)There is an even number of data points and the middle two are the
same.
Click to expand...

That's an excellent way of explaining it!

So, if you're using a MATCH formula to find the MEDIAN there's a good
possibilty that it may not exist.
 
Back
Top