Median Question

[KevinAGrogan]

I have two different jobs, and I track data on two different
worksheets for the jobs. I am interested in finding out the median
revenue earned for both types of jobs in each year (combined). I was
able to find the median for each type of job, now I need to know the
revenue for all jobs in each year. How would I set up an equation to
do that? I have the following data on each worksheet (in same
workbook):

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

I tried the formula below, but it gave me a number that couldn't be
the median:

=SUMPRODUCT(--(YEAR(Sheet1!C2:C1000)=X1),Sheet1!G2:G1000)+SUMPRODUCT(--
(YEA­R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000)


Thanks in advance for your help,

Kevin

 

[Toppers]

You asked the same question a few days ago ("Median for Year"and were given
several replies (not the one posted here which simply totals the revenue).

Did you try these responses?

If you found the median for the jobs, substitute the job column in your
formula by the revenue column?

 

[KevinAGrogan]

You asked the same question a few days ago ("Median for Year"and were given
several replies (not the one posted here which simply totals the revenue).

Did you try these responses?

If you found the median for the jobs, substitute the job column in your
formula by the revenue column?








- Show quoted text -

I am still unable to get the median for both sets of jobs combined.
I've tried the responses given in "Median for Year" but am still
having problems. I am able to get the median revenue for each job
independent of each other, but not the median for all types of jobs.

Thanks,

Kevin

 

[Toppers]

I don't know how (or if at all) you can "combine" in a formula both
worksheets. I have tried with INDIRECT but no joy. You may to merge the data
onto a single sheet and use the original formula.

 

[KevinAGrogan]

I don't know how (or if at all) you can "combine" in a formula both
worksheets. I have tried with INDIRECT but no joy. You may to merge the data
onto a single sheet and use the original formula.






- Show quoted text -

Thanks for your help,

Kevin