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
ADVERTISEMENT
Aug 2, 2006
I created two pivots:
1 provides sales data for companies in a time period,
2 provides market share data for companies in a time period.
Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields).
Attachment : calculate averages from pivot table.zip
View 6 Replies
View Related
Aug 2, 2006
I created two pivots: 1 provides sales data for companies in a time period, 2 provides market share data for companies in a time period. Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields). Check out my attachment too to symplify this question. How how i formulate this formula?
View 4 Replies
View Related
Dec 9, 2008
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
Oct 2, 2012
how to calculate average talk time in excel.
View 9 Replies
View Related
Oct 11, 2013
i get a problem in preparing a roster i went to ignore blank cells in calculating rank. and also i went to make the sum to be empty is one of the cells in a range is zero
View 1 Replies
View Related
Oct 4, 2006
I have a rather complex worksheet, but it needs lots of manual intervention, it calculates the number of staff needed at any given hour for a call center based on the calls offered for an hour, the calls for a day are copied into the calandar and the last 7 "Mondays" "Tuesdays" etc: calls are averaged to give me a call trend, what I need is a way of automaticly adding this data.
I have created dropdown boxes with all the dates, if I choose a date, I want to be able to fill the last 7 "Mondays data"
View 9 Replies
View Related
