Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median Question
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)+SUMPRO DUCT(-- (YEA*R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000) Thanks in advance for your help, Kevin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median Question
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? " wrote: 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)+SUMPRO DUCT(-- (YEAÂ*R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000) Thanks in advance for your help, Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median Question
On Jul 9, 9:04 am, Toppers wrote:
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? " wrote: 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)+SUMPRO DUCT(-- (YEA*R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000) Thanks in advance for your help, Kevin- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median Question
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. " wrote: On Jul 9, 9:04 am, Toppers wrote: 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? " wrote: 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)+SUMPRO DUCT(-- (YEAÂ*R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000) Thanks in advance for your help, Kevin- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median Question
On Jul 9, 10:12 am, Toppers wrote:
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. " wrote: On Jul 9, 9:04 am, Toppers wrote: 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? " wrote: 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)+SUMPRO DUCT(-- (YEA*R(Sheet2!C2:C1000)=X1),Sheet2!G2:G1000) Thanks in advance for your help, Kevin- Hide quoted text - - 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- Hide quoted text - - Show quoted text - Thanks for your help, Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median Date | Excel Discussion (Misc queries) | |||
Max/Min/Median | Excel Worksheet Functions | |||
median function | Excel Worksheet Functions | |||
median excluding 0's | Excel Worksheet Functions | |||
Median function | Excel Discussion (Misc queries) |