Calculate Average For Column Based On Moving Start Column?
May 3, 2013
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.
View 5 Replies
ADVERTISEMENT
Oct 2, 2008
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.
View 2 Replies
View Related
Jul 5, 2006
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.
Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
View 13 Replies
View Related
Mar 11, 2013
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
View 9 Replies
View Related
Jan 13, 2008
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 Related
Mar 15, 2014
I would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.
For example
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
Therefore in the current list the average is AVERAGE(A5:A14)=6.5.
The next entry added to the list will be in cell A4 making the list look like this
A4, 1
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5
I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.
as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.
View 2 Replies
View Related
Jan 5, 2014
I 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?
View 9 Replies
View Related
Mar 6, 2014
I am trying to calculate the average start time for a machine over a period. However the machine start time varies from 10:00 pm to past midnight. I have tried reviewing past posts but cannot seem to find a similar query.
View 13 Replies
View Related
Jun 4, 2014
Daily Result
14
10
16
10
5
15
8
12
15
9
I have the formula working for the average of the 4 lowest of all, using "AVERAGE" and "SMALL"; need for average of 4 lowest in most recent 7
View 6 Replies
View Related
Feb 11, 2014
I have an excel spread sheet with columns of dates I am trying to get an average formula that counts dates less than a year old as 1 and blank cells and dates greater than a year old as 0 and then gives me a percentage. For example I have a column with 10 dates all less than 1 year old I would get 100% but if I had a column with 8 dates less than a year old and 1 dates older than a year, and 1 blank cell, I would get 80%
Is this possible at all, or do I have to calculate the percentage for each column individually.
View 9 Replies
View Related
Dec 14, 2006
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 Related
Jan 19, 2013
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2:
Supervisor Name
October
November
December
Supervisor 1
8.3%
[Code] .......
View 7 Replies
View Related
Jul 8, 2013
I am trying to do a moving average and running into an issue with the count part of the formula.
Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.
My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.
My average should be 31 but I am getting 33.
This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))
I have tried using different Counta and ISnumber still not working
I would rather use a formulas then VB code.
View 5 Replies
View Related
Nov 7, 2009
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
View 9 Replies
View Related
Jul 29, 2014
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
View 6 Replies
View Related
Feb 26, 2013
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 Related
Nov 30, 2007
I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.
View 3 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
Nov 27, 2006
I was wondering if there is a way for excel to compare cells and identify when they are identical. My objective for this is as follows. Lets say i have some data entered in cells A1:A10. Suppose cells A1,A3,and A4 had the same value (112-343). Now suppose cells B1:B10 had values also, lets say B1=2, B3= 2.5 and B4=8. I need for excel to go through the A1:A10 range and realize when the A cells have identical values, after this is realized i want excel to take the values in the B cell that correspond to the identical A cells, average them and then store them in cell C. And if there are mutliple identical cells i would like the routine to be able to identify this also. lets say A1=A3=A4=(112-343) and A6=A8=A9=(154-456) ....
View 7 Replies
View Related
Jan 21, 2008
I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)
View 2 Replies
View Related
Dec 5, 2013
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
View 2 Replies
View Related
Feb 21, 2008
How do I calculate the average based on multiple conditions? Is my syntax incorrect?
Here's what i'm looking for:
=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))
View 9 Replies
View Related
Feb 12, 2009
I have some financial data that has been exported via sql from out financial system that lists transactions for each month in a column with 12 columns representing each month of the year in Sheet1. (My financial year runs July - June). The column headings are MONTHLY_TYR_BALANCE_01 thru to MONTHLY_TYR_BALANCE_12 where MONTHLY_TYR_BALANCE_01 = July, MONTHLY_TYR_BALANCE_02 = August etc.
To the right of this data I wanted to have one column that will list the MTD data each month ie in July this should reference to the column headed MONTHLY_TYR_BALANCE_01, and in August this should reference to the column headed MONTHLY_TYR_BALANCE_02
I can get the end user to type the month number into a field and reference back to this but I just can't think how to convert this to a column reference that I can then use.
View 2 Replies
View Related
Nov 21, 2007
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View 2 Replies
View Related
Feb 14, 2008
It's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?
It appears to be ok to me, but I don't want any errors when it comes to paying my employees
Formula: ...
View 4 Replies
View Related
May 15, 2009
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.
So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View 6 Replies
View Related
Sep 11, 2013
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
View 1 Replies
View Related
Apr 4, 2009
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
View 27 Replies
View Related
May 17, 2009
I 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.
View 13 Replies
View Related
Jun 14, 2013
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.
View 14 Replies
View Related
