I need to get the average of every 6th cell in a column, excluding errors --- three times.
The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8.
Is there an easy way to achieve these 3 averages?
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
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.
I have a formula in Cell A1. It tells me the cell location of the last number in column E. So, for example, I input the number 44 in cell e85, the value for cell A1 becomes $E$85.
Let's say I now put the value 33 in cell e86, now the value for A1 becomes $E$86.
I want to calculate the average of the values in column E, all the way from E1 to whatever is in A1.
Here's what I need: =AVERAGE(E1:<whatever is in A1, like $E$85>)
How do I write the AVERAGE formula to make this work?
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies View RelatedI have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.
Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).
Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.
For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.
For an example I have attached a month of February where there are blank date cells at either end of the column.
I am trying to calculate averages for a ratio I have calculated. Is there a way I can use VBA or Excel functions to average the G column depending on the value of the D column? In other words, find the average value of column G for all Industry Group = 103. Then, find the average value of column G for all Industry Group = 106. Then put industry group # in column H and avg. value in column I.
View 2 Replies View RelatedIs there a way to average every other row in excel?
View 7 Replies View RelatedI have a column with values from P2 to P3077. I need a formula that would average values from P2:P6, P7:P11, P12:P16 and so on till the end (that is 5 data points each time till the end).
View 2 Replies View RelatedI do not use excel very often but know there is a simple formula to take the average of numbers inputted into a column. For example, I would like to sum all the values entered into column “D” and then divide that by the number of entries
4
5
3
1
4
11
6
2
0
----
36/9 = 4
If I were to add another entry it would automatically change the divisor to 10
I want to get the average of various non-contigous columns, ex:
Column B G X Z
10 0 6 8
However, I need to exclude any zero values. In this case the correct average is 8. I have used various sumif's and Average(IF) functions without success.
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.
I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)
View 12 Replies View RelatedI want to be able to find the last non empty row in column C and then average the last three inputs. Thus utilzing this as a means for a projection of the remaining periods left in the year. All for projecting figures for the remainder of the year.
Bill
Period
Date
Amount
1
1/6/2012
3982.76
[Code] ..........
In sheet 2 b2, I want to calculate the average of the values in the last column of sheet 1. So if the last column is D then all the figures in D are added up and an average given. But if the last column is M then it gives me the average of that column.
View 9 Replies View RelatedHow to sum or average the data in the even column ( I mean column 2, 4, 6, etc) in a range (like range A1:DD1)? I tried to use Mod and Iseven to get it but failed.
View 9 Replies View RelatedI'm using Excel 2003, so no AverageIf.
I have 15,000 rows of data.
In Worksheet_1, I have:
Column A (Date) contains daily dates ranging from January 1968 to today, though not every day is listed (weekends and holidays are excluded).
Column E (Price) contains daily prices.
On a separate worksheet (Worksheet_2), I need to calculate average monthly prices, listed by end of month dates (Monthly_Date and Monthly_Price, respectively).
I've been trying to use an array formula like this:
=AVERAGE(IF(AND(MONTH('Worksheet_1'!A2)=MONTH('Worksheet_2'!A2),YEAR('Worksheet_1'!A2)=YEAR('Worksheet_2'!A2)),'Workshee t_1'!E:E,"0"))
I can get an average of the whole column, but I cannot seem to make Excel "grab" only the corresponding data in Column E that matches the right date criteria (in this case, when the month=January and the year=1968).
I am trying to average times from one column that is the difference of two other columns (generally just minutes). It is formatted in a military time format.
However, I do have criteria it must meet from another column.
Ex:
Col A Col B Col C Col D
1 23:57 00:07 00:10 "=C2-B2+IF(B2>C2,1) fx I am using"
2 03:15 03:20 00:05
1 10:30 10:42 00:12
That's the basics, now I want to average Col D but only if it is a "1" in Col A. There are some empty cells, being its formatted for 100 entries. I can get an average of Col D and a count of Col A, but I can't combine them.
I have a colomn with numers and text. I would like to get an average of the numbers in the column, but using average (A1:A10) gives me #N/A as some of the cells contain #N/A.
So wondering if there is an AverageIF function in excel that I can use to write something which only averages IF it is a number.
Sounds very basic but I could not find anything through search.
I have a table full of phone numbers and call durations.
In A2:A6160, I have 162 different phone numbers and in U2:U6160, I have the call durations for calls made to those numbers.
In a separate spreadsheet, I have each number listed in Column A and in Column D, I want to display the average call duration for each of the numbers.
I have a problem and I found a good explanation of something similar here:
[URL]
In the original posting the following formula solves the problem (attached) TestData.xlsx
B1: =AVERAGE(INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)):INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)+4))
What I would like to return is the average of the 1st 5 values, but what if a number of cells after the first value were also blank
I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example
comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5
I would like that to become
comp 1409 99.73
comp 1308 65.7
comp 1509 99.5
Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative. Example:.....
This code inserts a new column to the left of Column CV ...
View 6 Replies View RelatedI am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0.
I have a list of 186 random numbers sorted by descending order in column A. In column B I have another statistic of corresponding values to the number in column A. I am hoping to calculate the average of values in column B based on the percentiles of column A. (i.e. for the top 10th percentile values of column A the average B value is X, for the top 20th percentile of values in column A, the average B value is Y).
View 2 Replies View RelatedI have a DATEDIF column which I have used to calculate the age of a group of people from their dates of birth. This has produced results in cells which look like this: Age is 44 years, 1 Months and 28 Days
Where I am stuck is that I need to calculate the average of this column so I get the average of the total group. Is this possible?