Calculating An Average But Excluding Boxes With Text

Sep 25, 2009

I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer.

The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week.

I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people.

I think the spreadsheet attached gives a good idea of the system and what I want to do.

View 12 Replies


ADVERTISEMENT

Calculating Average Excluding Cells Containing <?

Jan 26, 2014

I am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".

View 4 Replies View Related

Excluding Zeros When Calculating An Average

May 10, 2007

I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10

how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)

I would like the answer to be 15.....I still need the zero's in the rows however

View 9 Replies View Related

Calculating Column Average Excluding Blanks?

Apr 26, 2012

Project: x
Activity: xActifity TypeReference FormDec-11Jan-12Mar-12Apr-12May-12Feb-12Mar-12W/EW/EW/E2-Mar9-Mar16-Mar23-Mar30-Mar6-Apr13-Apr20-Apr27-Apr4-May11-May18-May25-May77%91%91%R/S95%100%100%R/S100%100%100%100%MMMM100%71%71%R/S100%100%R/SMMMM97%81%100%R/S100%MM100%R/S75%85%R/SR/SMM100%100%96%Average

Basically what I want to achieve is the average % of Dec 11 (D11:D22 on the sheet im working on) scores but excluding any blank fields (not excluding potential scores of 0%)

View 2 Replies View Related

Average Out Range Of Cells Excluding Text Found In Other Cells

Jul 17, 2014

What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"

Would that look anything like:

=average(F4:F343)/countif(Q4:Q343, "<>FMC")

I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.

View 2 Replies View Related

Average Excluding If-error Data

Apr 13, 2014

I've got set of data in columns B, E,H,K, N, Q, T and W. Some of columns are with if-error and some are not (Attachment). I want to find the average excluding if-error data columns. Another way, columns with if-error data should not be counted when average is calculated(In column X).

View 3 Replies View Related

Average Over Worksheets Excluding Zeros

Dec 14, 2009

im trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:

View 14 Replies View Related

Average Of Every Other Cell Excluding Zeros?

May 9, 2013

I current have an Excel doc that I am trying to pull averages from. Every row has data that needs to be averaged but I only need every other cell. And I cannot have zeros in the average because it will skew the data. Also if the average for a row ends up being 0, I want that average to show up as 0 not #DIV/0!. From Column H to EE I need to average each row individually.

I came up with the below formula.

=IF(SUM(H1:H1:J1:J1:L1:L1)=0, "0", AVERAGEIF(H1:H1:J1:J1:L1:L1, ">0")

It works but Its not practical because I have to manually enter all of the cells that it pulls from.

View 14 Replies View Related

Weighted Average Excluding High/Low

Jan 6, 2009

I'm trying to calculate a weighted average excluding the top and bottom value, and am having trouble creating a formula that can be dragged across.

my values are in E2:L17, and the weights are in M2:M17. Basically I want a weighted average of each column, using M2:M17 as the weights.

I'm sure someone's done something like this before, care to assist?

Example:

Data: 1, 2, 3, 4, 5
Weights: 3, 4, 5, 6, 7

Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3

View 9 Replies View Related

Calculating Lead Time Between Two Dates / Times - Excluding Holidays And Weekend

Apr 18, 2013

Calculating Lead time (in hours) between two dates/times, excluding holidays and weekend

Start Time
End Time
Lead Time

12/26/2012 15:50
1/2/2013 12:38:00
??????????

View 4 Replies View Related

Calculating A Rolling Average

Oct 8, 2009

I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...

I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?

Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly

View 10 Replies View Related

Calculating Average Of A Range

Oct 9, 2009

What could be the syntax (in a code) for averaging a range in col C. the range values are given in E3 and E5. (E3=508 & E5=1200) These values changes each time I open the workbook.

View 2 Replies View Related

Calculating Average Of 2 Averages

Jun 7, 2013

I have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.

I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:

=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)
+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)

But if I use the same for the average scores is works fine until a person covers two duties:

=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)
+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)

This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?

i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.

View 1 Replies View Related

Calculating Average From Averages

Oct 1, 2013

I have a database with monthly tabs and a summary sheet, on the summary sheet I calculate an average length of stay for each month (tab) but I need to find the yearly average and I don't know if there is a formula that would calculate the true average by using the monthly averages.

View 2 Replies View Related

Calculating An Average From Letters

Feb 11, 2008

I have a table with 9 columns filled by a letter (A, A-, B+, etc). This letters correspond to grades (4, 3.67, 3.33, etc). The tenth column is supposed to contain the numerical average of all 9 letters in the line.

View 9 Replies View Related

Calculating Average Values

Jul 28, 2006

I created a pivot table in which sales amounts are represented and a derived table in which market share percentages are showed. Now i am looking for a formula that is able to calculate average market share values. Depending on which country i selected in the pivot table and which category, the number of active companies are changed. How can i formulate this formula to calculate average market share percentages in a certain period. check out my attached file to clarify the situation.

View 2 Replies View Related

Calculating AVERAGE DEVIATION From A SPECIFIED POINT?

Jul 29, 2009

I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).

View 14 Replies View Related

Calculating Weighted Average With Criteria

Aug 25, 2009

I have a data set that looks something like the following:

TypeAmountCost
A100$50
A200$40
A300$35
B250$40
B275$55
B500$60
A700$20
B350$25
B450$35
C400$80
C400$60

I am having trouble creating a formula that would calculate the weighted average cost depending on the type (i.e. weighted average cost of $28.84 for A, weighted avg cost of $70 for C).

View 2 Replies View Related

Formula For Calculating Average Time?

Sep 24, 2010

I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?

View 5 Replies View Related

Calculating Average Based On Other Criteria?

Feb 27, 2012

I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.

What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.

eg.

0.1562818960
0.1558039690
0.1553260431
0.1548481172
0.15437019 2
0.1538922643
0.1534143384
0.1529364124

[code].....

In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)

View 2 Replies View Related

Calculating Average Of Numbers Put In One Cell

Feb 13, 2013

I am trying to find a way to average all the numbers placed in one cell with the answer in another.

So in cell A1

1, 2, 3, 2 or 1-2-3-2

In cell A2 would be 2. But the amount of numbers in A1 will vary from time to time.

View 9 Replies View Related

Calculating Average Using Vlookup Table

Dec 18, 2008

i have data on excel which has the following fields:

Invoice No,Inv.Date,Month,Customer,Part Number,Description,Qty.I want my final pivot table to have the following results for every part no. and description

Total monthly sales

total year sales

and for each model sale calculated as sale quantity / week

I have tried the following way but it did not work:

just next to the pivot table under the heading"AVG" i used the following formula to get the sale/week on cell o6=grand total sale /(applicable no. of weeks).and when i am copying the formula to the cells lying below the formula is being applied as that of the first cell so getting the same result for all the rows.is there a way to calculate sale/ wk within the same table

View 9 Replies View Related

Ignoring Cells While Calculating Average

Oct 18, 2009

I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.

This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.

View 9 Replies View Related

Calculating Average Based On A Condition

Jul 27, 2007

I need to calculate the average of values in a particular column if some other column contains a particular text. For e.g. I would like to calculate average age of all the people who belong to the Comapny "XYZ". Which means that I have two columns here one is "Age" and the other one is "Company". So For all the people who belong to the company "XYZ", I need to calculate average age.

Company Age

XYZ 24
ABC 26
ABC 34
XYZ 30

My average should be (30+24)/2 = 27.

View 3 Replies View Related

Calculating Rolling Average Ignoring 0 Values

Jun 6, 2014

What I have is a spreadsheet with a whole bunch of sporting data on it. see attachment for an idea of the layout, Stats.jpg

In these data each round a player is given a rating. What I want to do is calculate is the average rating for each player across the last 3 and 5 games they have played. When a player misses a game they get a rating of 0 for the round. I want 0 ratings to be ignored in the running averages. Additionally, games yet to be played have a 0 in the cell and should be ignored. To get overall averages ignoring 0's was simple I used the averageif function, but I couldn't figure out how to use that but only extract the last 3 non 0 values. I currently have data up to round 12, so if a player has played in 12, 11 and 10 then the 3 round average should just be the average of these 3, but if they missed for example round 11 then the 3 round average should return the average of 9, 10 and 12.

The data is set out in the following way.

In column B I have a list of player names from cells B3:B618.

Across row 1 from N1:AM1 are labels "round 1" "round 2" etc.

The ratings for each player in each round are in N-AM and from 3-618. i.e. N3:AM3 contains the weekly rating for player 1, 4 contains player 2 etc.

I would like the 3 round averages to be returned for each player in column L and 5 round averages in column M.

View 5 Replies View Related

Calculating Average Days Overdue Per Department?

Jan 20, 2014

I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx

View 2 Replies View Related

Calculating Average Number Of Staff Per Week

Aug 4, 2014

I want a formula to calculate the average number of staff working per week in the attached data.

View 9 Replies View Related

Calculating Average From Range Of Data That Changes Size?

Mar 24, 2014

I'd like to work out the average of a set of data which changes length and position. It's probably easier if I attach a workbook.

The different sets of data are split by blank cells so they can be distinguished.

View 6 Replies View Related

Calculating Monthly Average Based On Total

Mar 26, 2014

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 Related

Macro Or Excel Function For Calculating Average

Apr 16, 2014

Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....

I had done manually on the right side in the attached excel sheet. only those yellow markings.

Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,

[Code] .....

Attached File : average for watersd OF 100TT.xlsx‎

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved