I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.
When i use the following f(x):
{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000
I've read several threads about using Median(If, and I have success with one criteria, but not with three. Here's the setup:
I have a log of phone calls, and I want to find the median call length for calls in a certain month or set of months (i.e. a quarter, three months). In Calls!B I have the date of the call, in Calls!C I have the call length in minutes (i.e. "34"), and in Calls!A I'm using Concatenate and Text to return month/year (i.e. "Aug06").
With one criteria, it calculates the median correctly. With three, it only returns #NUM. Yes, I hold control+shift and hit enter. Here's my formula:
=MEDIAN(IF(Calls!$A$2:$A$500=Scores!B8,IF(Calls!$A$2:$A$500=Scores!C8,IF(Calls!$A$2:$A$500=Scores!D8,Calls!$C$2:$C$500)))))
Where Scores!B8,C8,D8 hold "Oct06" "Nov06" and "Dec06" respectively. What am I doing wrong? Is there another approach I should use instead?
I have Problem column, with problems from A2 to A14. I have number of calls column, with number of calls from B2 to B14 and finally i have percentage column in which i want to automatically calculate problems percentage. I'm including my exel file.
View 5 Replies View RelatedI have 3 sets of data for two different groups:
Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?
In A2:A9 are text "time" numbers which may cross midnight
1941
1852
0130
2347
0242
2326
0028
2257
Looking for formulas to place in A10:A11
which can return the average & median of the times in the same text "time" format
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.
View 2 Replies View Relatedcalculating the number of calls logged in a shift, based on a 3 shift system in a day. shift 1 from 08h00 to 16h00, shift 2 16h00 to 24h00, and shift 3 00h00 to 08h00. each shift receives a number of calls based on the time the call was submitted.
View 2 Replies View RelatedWant to be able to count number of calls (C in the rows) of each provider by DOW (labels in the column).
View 4 Replies View RelatedWe are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?
View 8 Replies View Relatedhow to find the median for a list of numbers using =median(). But what if I have a list of prices for different types of say cars.
Car Type Price
A $10000
B $15000
A $20000
C $30000
Is there a macro that will return
Car Type Median
A $
B $
C $
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View Relatednumber for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies View RelatedI am trying to combine sub total if the formula matches a certain criteria.
I would like an average of the data I have previously used an average if formula =averageif(D:D,L4,J:J) but now I would like the answer when I filter the data.
=SUMPRODUCT(--($A$2:$A$12=$D$24),--($F$2:$F$12=$B$25),($H$2:$H$12))
This is the current formula I have, however I am working with averages. After the first two arguements are done I want the overall average of columun H.
I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B.
In other words find the average price for the total of each item..
A B C
Code Qty $
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2
I created a spreadsheet to track my bowling scores by date by lane
Date Lane game 1 game 2 game 3 total
What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.
I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
View 1 Replies View RelatedOn a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.
if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.
[Code] .....
I am trying to get and average price for a total quantity of jacks
Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9
I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?
I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.
Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)
Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.
View 5 Replies View RelatedI have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies View RelatedI have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies View RelatedIn my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".
I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.
I need a formula where by i can get just number with total in front from e.g cell D2 =148513 Total and E2 should result just 148513 and leave the rest blank which does not have total.
Layout
148513
148513
148513 Total
1578972
1578972
1578972
1578972 Total
Col A Col B Mon Tue Wed Thu Fri Sat Sun Total
1000 Susan Y Y Y Y Y CountA(B6:B12)
899 Ted Y
1560 John Y Y
1000 Susan Y Y
etc.
I need to be able to total the number of Y for a given person
in a week as well as a total for the person over 4 weeks.
CountA works for the week But what formula would I need to count
for a specific person over 4 weeks. Col A is in random order. If the person
has no Y the answer needs to be blank and not N/A.