Find The Average Values
Oct 4, 2008
Column B: The day of the week that the date is, Sunday, Monday, etc.
Column C: Every date in 2008
Column D: my data (the number to be averaged)
What I need the formula to do:
Look in Column B for every instance of "Sunday", and count them up (they count only if the data in column D is not 0) and use that number to average the number in column D
So if I have 52 Sundays and we are only 4 weeks into the year and the 4 data points are 50, 100, 75, 25. The average will be 62.5 instead of 4.8 (divided by 52).
View 6 Replies
ADVERTISEMENT
Apr 20, 2013
I am trying to calculate the average of the absolute values of a range of visible cells. I use filters to extract the data rows I need from a large spreadsheet. Then I need to calculate statistics on the resulting data, i.e. the visible cells. The line I am using to calculate this is:
VB:
ThisWorkbook.Sheets("Tracker Channel Stats").Cells(newRow, 4).Value = WorksheetFunction.Average(Abs(R.SpecialCells(xlCellTypeVisible)))
However, what I get back is simply the value of the first visible cell in the range, i.e. the average of the absolute value of one number is that number. how to make this work
View 1 Replies
View Related
Feb 23, 2010
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
View 11 Replies
View Related
Dec 23, 2011
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
View 9 Replies
View Related
May 25, 2008
I have 3 excel sheets (A,B and C) linked to a master sheet (X). In each sheet (A,B and C), people enter unique idetifier in first column, its type (mr, ab, J) in the second column, start date in the third column and finish date in the fourth column. At the end of every week, i subtract each start date from the finish date and get the number of days spent for each unique idetifier (column N). I preiously posted a thread (Calculate difference between dates excluding weekends) to inquire about how to calculate the difference between finish date and start date and exclude the weekends. I received some nice solutions, but none has worked as the start date cane be weekend as well.
My second and major query is once i have the difference in weekedays for each unique identifier in all the three sheets, I want to calcuate the average of each identifier type (mr, ab and J) in the master sheet. In the master sheet, i have used sumproduct to calculate the average of each idetifier type from column N for each sheet. That means i have a column for A sheet, subdivided into three types, which include the average. I am using sumproduct for this. Once this is done for each sheet type, i need a final colum in the master sheet that calculates average of all mr from A, B and C together, and similarly for ab and J. The problem here is if i combine all the sumproduct formula, i am not gettng the right answer.
My formula looks like SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40)) for all mr in sheet A. I then divide this by SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>"")) to get the average of all mrs in sheet A. I do the same for sheet B and sheet C for all three identifier types. Now i want to combine the formula for all sheets together under mr, Ab and J. I thought the following should work ((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40))+(SUMPRODUCT((SheetB!$E$5:$E$40="mr")*(She etB!$M$5:$M$40))+(SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40)))/((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>""))+((SUMPRODUCT((SheetB!$E$5:$E$40="mr") *(SheetB!$M$5:$M$40<>""))+((SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40<>""))).....
But this is giving me wrong value. It is working fine when done for individual sheets, but when combined for all three sheets together, the value is wrong.
View 9 Replies
View Related
Nov 27, 2008
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
View 7 Replies
View Related
Jul 27, 2009
Attached Dummy file. I would like to be able to get the average time spent on a process for the day recorded on the "Results" tab. I have posted the answers it should be on the Results tab, which was done manually. I would like the file to do it on it's own. On the individual's tab, the time and instances will be recorded for each job. I believe I am close, and the only thing I need is the Results page to be able to return the desired results. Ranges varies day-to-day, and I think (maybe), I can figure out the dynamic range settings.
I would like to avoid creating a helper column for each set, because then I would be dependent on the person expanding on this. I am also avoiding Pivot Tables, I understand it, but not the ones that needs it. This will have about 15 tabs for employees, and could be possible that more or less will happen, depending on workload. This will continue to be manual for now.
View 5 Replies
View Related
Jun 2, 2009
I have a total in F16 that i need to find an average of.
The range is set from f4 - f15 (12 months) and need to find the running average per month. but as we only have done 5 months of the year so far that is the only average required (F16/5).
I need this to count at the end of june (F16/6) basically not counting any zero months (jul, aug, sept etc until the end of each)
View 5 Replies
View Related
Jun 12, 2009
I am just looking for an function which will gives me the average of the hours worked on daily basis. A breif descripiton is i have an workbook in sheet1 i have the sample report which i struggle to prepare on daily basis for each client.
Sheet2 i have the Raw data in column H i have the client name and then in column I is Date and finally in J the hours we worked on in it. To say i just want the average if the each client on the particular date as shown in Report sheet.
View 2 Replies
View Related
Apr 20, 2012
I want to find the average of a range.
I've put the range in order and I want to take off the top 7.5% and the bottom 7.5%. Then find the average of the data that is left. (the middle 85%)
Can I do this with a formula?
View 8 Replies
View Related
Jul 10, 2013
I want to find incremental average
My cell numbers are
AG4
AG4,AG13
AG4,AG13,AG22
AG4,AG13,AG22,AG31
and so on..
(In incremental of 9)
View 9 Replies
View Related
Jun 25, 2014
I have the following Data:
Full Drum
247
Full Drum
247
Full Drum
261
Full Drum
261
[code]....
I need to know how to find the best combination of combining these drums to get the highest average from each combination. Each combo will have 3 drums except for one which will have two and a half.
View 2 Replies
View Related
Apr 12, 2008
In the table below I'm trying to work how trend is being produced but I can't figure it out.
I have found how to find the moving average =AVERAGE(C2:C5)
I've tried a few methods using the trend function but I really don't understand what I'm doing. how its being calculated by looking at the table?
View 9 Replies
View Related
Aug 4, 2009
In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.
I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.
View 23 Replies
View Related
Mar 14, 2014
Let's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
View 4 Replies
View Related
Jul 31, 2014
Ive been given the data for bond pricing for two tenures i.e. 15 years and 20 years . I needed to find the average pricing for 16.5 years , whether excel is able to compute such pricing?
View 5 Replies
View Related
Oct 16, 2009
I have two columns: 1) Last Date of each month from 2005 and 2) Value.
Date format: dd.mm.yy
For example:
31.01.05 1.7
28.02.05 -0.7
31.03.05 2.6
30.04.05 0.4
....
How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
View 3 Replies
View Related
Aug 14, 2013
I have attached an excel file. What I would like to find is the average of column D (number) with respect to column A (Genotype ) and also different wave length (Column C). i.e.:
Genotype 1 to its corresponding value in column D for wave length 450
Genotype 1 to its corresponding value in column D and for Wave length 470 to and so on.
Attached File : test2.xlsx‎
View 6 Replies
View Related
Feb 8, 2013
We are trying to track the average star values of reviews posted to our clients accounts. Here is an example of the spreadsheet we have set up:
Company Name
Review 1
Review 2
Review 3
Review 4
Review 5
Company 1
[CTY] 4 Stars review content
[ISP] 5 stars review content
[GGL] 4 stars review content
[CTY] 3 stars review content
[YELP]5stars review content
In this example, we use the initial [ ] to track which site the review was posted on ex: [CTY] means city search, [GGL] means google, etc.
We want to be able to track the average star value on each site. So the formula for City Search would scroll across the row of reviews, pick out reviews 1 and 4 and then find the first number in each and average them. The result in this case would be 3.5
From the research we have done so far, this looks like it will require a complex array formula. How to make this formula? Is this even possible what we are trying to do?
View 5 Replies
View Related
Jul 23, 2014
I am using Excel 2010 and I am currently trying to find the Avg Min / Avg Max costs for 4 different groups.. Players, Accompanying, Coaches, Medical.
I need it to get the minimum values from 3 different sections ( I am using custom names for them such as PlayerReg for Player Registration costs ) once it gathers the minimum / maximum values from the 3 different sections it needs to add them together to display the Avg Minimum / Maximum for each group.
At the moment I have =MIN(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Minimum /w Frequent Flier although the minimum value it should detect is $948.75 it displays $1115.
For maximum I have
=MAX(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Maximum /w Frequent Flier although once again this value should be $1174.50
Although for some reason it displays $978.75 - which is the minimum cost. The Maximum figures for the other groups (Accompanying, Coaches and Medical) don't even display values Instead it just has #VALUE! in which I'm guessing I have something wrong with it.
View 2 Replies
View Related
Oct 1, 2008
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
View 9 Replies
View Related
Oct 21, 2009
[for excel 2000]
Hi chaps, I have a spreadsheet of lap times that I'm trying to generate an average time from. data looks a bit like this (in time format hh:mm:ss) :
01:23:34
01:44:23
01:56:34
01:23:56
-you get the idea. problem is, when calculating an average I get either #DIV/0!,#VALUE! or 00:00:00 depending on the approach I use (the column is definitely time format hh:mm:ss), so far I've tried:
=AVERAGE(H4:H433)
=AVERAGE(IF(H4:H433,H4:H433))
-and so on, none work all give a variation of the aforementioned errors. I've tried changing the column format to number, copying all the data to a new sheet, still no joy. This should be very simple, any idea what am I missing?
View 9 Replies
View Related
Jun 29, 2006
I am trying to find a formula that works out the average of the figures in column K.....my problem is that so far in column K there is only one positive number (11)- the rest of the cells are DIV/0 so the average is showing as DIV/0. This is incorrect as the average is really 11 - how can I sort this out with a formula?
View 9 Replies
View Related
Jul 31, 2006
I need a formula that will look for a name in column "A" and if it find that name it should average the numbers in column "E" to "I". So if TEST ONE is found in A5 then average E5:I5.
View 3 Replies
View Related
Aug 13, 2007
I have a list of values on my spreadsheet in a similiar order to this:
17.91
16.59
15.00
14.86
13.56
12.17
11.01
0.0
I use the average function to work out an average for all the values, but since the value of 0.0 is showing i should not include this as an average. the range of values above (8) will always be the same for the month - so i need to find a way of omitting the 0 value without keep changing the formula
View 9 Replies
View Related
Apr 5, 2009
Need to find average (see attached file) of certain rows of column C that correspond to letters on column B that match any letter on column A. Basically if column B has the same text as in one of the cells in column A then number across in column C should be used to calculate the average. In column D I have entered(manually) all values that satisfy the above condition and obviously the average should be 1 if formula is correct.
I have tried to use AverageIF function but not able to do it as there are many conditions. Another thing to consider is that conditions change (column A content) so I need to use cells that have information rather then actual text in it.
View 5 Replies
View Related
Aug 5, 2009
First off, I hope one of you will end my suffering . I have been googling this thing every which way and haven't found my answer to what seems like a simple question.
What formula will find the last cell with data in a column (in say column 'B') and average backwards 30 data points? The last cell changes constantly, but I need the last 30 averaged consistently.
View 13 Replies
View Related
Mar 6, 2014
I need to find the cells that are greater than 48 and then find the average of this number.
Is there a formula that could put this together?
Average if greater than 48.xlsx
View 1 Replies
View Related
Feb 27, 2014
I have the following code to go through various values in a column and colour code them. However I am facing two issues here:
1. I need to generalize the code such that it runs through column G till column S in different worksheets
2. Few columns between G and S do not have any data and the code is getting stuck at the line below:
myResult = WorksheetFunction.Average(Range("G:G"))
View 1 Replies
View Related
May 7, 2009
formula to do the following:
Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.
So in a 5 cell range the values could be:
blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
View 9 Replies
View Related
