Free Microsoft Excel 2013 Quick Reference

Average time not calculating Results

I have a simple AVERAGE function, it averages the call time for all agents. I am not having troubles with the function itself though. The problem is when I copy and paste the information into the workbook, the information shows, but it does not "average" the time. I have to double click on each cell individually, after I double click on every cell with a time in it, it then averages the call time. Why does this not work right after pasting the information in? I have also tried doing a "paste special" but that did not work either. Thanks in advance!

Hi, ive been racking my brains trying to solve this and im sure theres a simple solution but can't seem to work it out.

Im trying to have a button on excel which will be single clicked 4 times and then an average time between clicks will be displayed.

I dont mind if the average is from the 3 intervals between clicks ( 1-2, 2-3, 3-4 ) or and average of the time between clicks 1-2 and 3-4.

(if this proves easy then im also looking for an error message if the times are not within 10% of each other, but ill cross this bridge first )

thanks for any help

Rich

Hello All,

I would like to calculate the average time between sales of a product, but I can't quite grasp the formula I need, so I'm looking for some help.

Here's my dataset:

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=

ABCD1MaterialWeek NumberSales2Apple1103Apple24Apple35Apple46Apple57Apple6108Apple79Apple810Apple911Apple101012Apple1113Apple121014Apple1315Apple1416Apple1517Apple1618Apple1719Apple1820Apple1921Apple201022Apple2123Apple2224Apple2325Apple241026Apple251027Apple26Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The formula would establish the length of the array from Column A (in my example, the Material is 'Apple' and the array is 26 Rows long).

I know that I need something that counts the number of weeks where a sale exists, but I can seem to get my head around how it'd establish the length of the array, so that the division can take place to establish the average.
Any help most welcome!
Regards,
Matty

I'm trying to track the average time a person uses a piece of equipment at
work. The average function does not seem to recognize time less than 1 hour
(i.e :37) in duration. I have about 30 occasions when the equipment was
used-- ranging from :05 to 3:10.

I appreciate your help.

OK, this may be a dumb question, but here goes...the scientist for whom I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:
Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?


	VB:
	
 ColAve () 
    Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hey guys.. I have a list of DAILY data on many pieces of equipment in our company from back in 2001 until current. I need to produce graphs that show MONTHLY averages over the years. As you can imagine, it would take quite a bit of time to go do this manually for each month. I don't want to use a pivot table of any sort because I'm hoping to have this code as a sort of "in house program" for future use when this task needs to be performed. The "raw data" sheet is like this (example):

Column A Column B
04-May-01 2340
05-May-01 3523
... ...
03-Jan-11 5349

I want the monthly averages on a new sheet. Here is what I have right now:


	VB:
	
 PerMonth() 
     'Set you worksheets
    Set s1 = Worksheets("sheet1") 
    Set s2 = Worksheets("sheet2") 
     'Clear current data
    s2.Cells.Select 
    Selection.ClearContents 
     'Initialise variables the first date in your set
    intOutRow = 1 'Excel is not zero-based
    intYear = Year(s1.Cells(1, 1).Value) 
    intMonth = Month(s1.Cells(1, 1).Value) 
     'Loop through your data
    For i = 1 To 5000 'or length of data
         'Check if date has changes during loop
        If Year(s1.Cells(i, 1).Value) = intYear And _ 
        Month(s1.Cells(i, 1).Value) = intMonth Then 
             'Month is same so add to values in sheet 2
            s2.Cells(intOutRow, 1).Value = Str(intYear) + " - " + Str(intMonth) 
            s2.Cells(intOutRow, 2).Value = s2.Cells(intOutRow, 2).Value + s1.Cells(i, 2).Value 
        Else 
             'Increment the output row and reassign Year and Month
            intOutRow = intOutRow + 1 
            intYear = Year(s1.Cells(i, 1).Value) 
            intMonth = Month(s1.Cells(i, 1).Value) 
             'Month is now the same so add to values in sheet 2
            s2.Cells(intOutRow, 1).Value = Str(intYear) + " - " + Str(intMonth) 
            s2.Cells(intOutRow, 2).Value = s2.Cells(intOutRow, 2).Value + s1.Cells(i, 2).Value 
        End If 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As of right now, this code is producing monthly sums but not calculating the average.
It seems so simple but I can't figure it out. Can somebody please help? Thanks!

Hi All, thanks for your time.

I have a worksheet model with a number of UDF's that are iterative in nature, i.e. they take inputs and loop until certain limits are met before returning their value.

The are quite resource hungry as they are present in a lot of cells in the workbook, a typical calculation (the workbook is set to manual) takes around 30 seconds.

I am conducting a sensitivity analysis using this model and have written a routine that enters the model inputs and then transfers the outputs to another worksheet for further analysis. This process is also iterative as the inputs are varied within defined ranges which ends up with as many as 5000 iterations... Well hopefully, at this stage I have only tested up to about 50.

The problem becomes apparant with a userform that I utilise as a record of how many iterations have been completed, the average time for each and a suggested completion time. I can run the routine several times without incident and then I receive a runtime error 75, cannot find object. On ending code execution I am unable to open the userform in the project explorer window, double clicking it has no effect. At this point if I attempt to save excel crashes and I am asked if I want to auto recover.

Interestingly, if I attempt the exact same operation again as soon as I am back in the document, it works. There must be some logical explanation as to how this happens but I am stumped.

I have found the following similar posts (I am not the author of any of them):

http://www.ozgrid.com/forum/showthread.php?t=141301
http://www.mrexcel.com/forum/showthread.php?t=476666
http://excel.bigresource.com/Track/excel-qb63LcPq/

But alas there are no clues to be found.

I have followed norie's advice on the first link above, i.e. "Goto Tools>Options... and on the General tab check Break on All Errors." And will look to see what line of code is highlighted when it happens again. I am not sure how to recreate the fault as it really does appear to happen at random (very unlikely!).

Happy to post whatever code might be pertinent to getting to the bottom of this.. Just not sure where to start at all.

If any VB savvy folks have the time to look this over, I'd be very grateful.

I was able to record a macro that did essentially everything I wanted it to, with the important exception of getting the right selection for the average=() calculation.

My worksheets are set up with multiple variable columns, and multiple data collection stations as rows. The rows are sorted by date, with a blank row dividing the data by month. There may be 50 rows in January, then a blank row, then 4 rows in February, then a blank, then 15 rows in March, etc. I can easily average the monthly values in, say, column N by typing "average(" in the cell where I want the average to be calculated, then selecting the top cell for the month of interest in column N, holding the shift key down, pressing end, pressing the down arrow, and then typing ")".

The macro recorder, however, doesn't seem able to handle this method of selecting the cells to put inside the formula parentheses. If I try to run the created macro on other months, it will average the same number of cells targetted by the initial example (i.e. the month I was working with when recording the macro).

Is there any way to code this operation so it will vary the number of cells averaged, based on the number of data rows present in each monthly group? My research into coding averages is turning up only examples that deal with whole columns or fixed cell ranges.

Since I have a pretty big stack of worksheets to work though, it would be very nice to automate the process, but I'm still fumbling here. Not getting anywhere.

fingers crossed

I am having the hardest time understanding what is going on with my spreadsheet. I have a spreadsheet that is taking four different data inputs and adding them up to figure out what percentage of that work type as it relates to the average processing times. This is the formula that I am using for 510K the bottom

=((AK11/(AK14/'Workload Distribution'!F13))*(AK9)/(SUM(AK9:AN9)))

F13 being the average processing time of 5 hours.

What I don't understand is the second field for Internal Requests is:

=((AK11/(AL14/'Workload Distribution'!G13))*((AL9)/(SUM(AK9:AN9))))

The percentage that is coming out seems to be problem because when I manually put the percentage in it calculates fine. If I do not the values come out the same even though they are not. Can someone explain to me what I am doing wrong. Thank you very much.

May510kInternal RequestsEIRPMA18300Required Monthly Hours146Hours Logged by Work Type510kInternal RequestsEIRPMA90900Actions Needed by Work Type510kInternal RequestsEIRPMA1146494Break Down TotalPagesBoxesAvg Page TotalAvg Box Total302093020162Hourly Breakdown by Work Type510kInternal RequestsEIRPMA6.946.94

I've done an extensive search of the forums, and haven't come across a solution to this tricky little problem!

I've got a 52 week chart that graphs overtime as a variance to plan.

The graph takes the data from the actual column and then charts it for me on the right. The cells in the actual column take data from another spreadsheet in the workbork, and as a result, if they don't have any data in the previous workbook, they show up as a zero in the new column. Typically this is an easy fix with a NA() formula addition.

Here's my problem. I've got an average calculation in the total column to provide me with an ongoing YTD overtime calculation. If I use the NA() in the formula to have the graph ingnore the zero data fields, it seems to mess up my average calculation at the bottom of the column. I've provded an example of my average formula below. Is there any way to build a formula in the "Data Entry" cells that are linked to the previous worksheet that will provide my graph with a NA() type function or the line graph doesnt hit aero, while at the same time not have an impact on my average formula?

Hi everybody,

It's my first time here, Could you help me please

As shown in the image above

The part in red is not available at start

I need to calculate the average price of each type of Description Automatically.
Here only 2 type of Description appear for simplicity

1. Bureaux
2. Bureaux 2 rooms

The Average Price for Bureaux is calculated

i.e From C6 to C11 ..

and place at the end of the sheet or else, but what is more important is the calculation to be done to calculate for each type of Description available, its corresponding average automatically.

The average can be calculated and place just below last row ...

Thank you very much for your kind support
If there is any more infomation you wish to have, dont hesitate to ask me

James

Hi all,

I'm trying to generate the average time for a number of finishers in a
sailing race. There may be certain boats that have text values in place of a
finish time for did not finish, did not start or did not compete.

Q1 - The spreadsheet I inherited used SUM() to give the sum of the finish
times, COUNTIF(value >0) of the count of the number of finishers, finally to
arrive at average = SUM / COUNTIF. This gives errors as SUM() does not give
the correct value when the text values are present - are there functions
available to give the sum of the race times for the finishers (this figure
also used for other stats)?

Q2 - Does the AVERAGE() function ignore text when calculating the average?

Cheers!
Mark (Skiffie)

Hi,

Iím trying to achieve the following from macros in excel sheet. However Iíve stepped in, up to some level but stuck then after.

Iíve following data in my sheet under column A to C:

Column A contains some redundant IDs, B contains some values in hours and C contains max hours for certain set of IDs.
Iíve macro that performs the task and prints the data into column E to I which is as below,

Column E contains removed Duplicated IDs and Column F contains Frequency of occurrence of IDs, column G is the Average value in hours for each ID and Column H is having the Average of Max hours. Column I contains the Index value.

For ex. ID MVC4832 occurred 4 times in the first list then second list will contain MVC4832 only once along with its frequency and average time.
Index Value is calculated using Formula = (Avg. Max Hours Ė Avg. Hours) X Frequency.
So far it is working for my requirement.

The macro for the above task is as:


	VB:
	
 
Sub ReportFrqAvg() 
     ' Stanley D. Grom, 12/05/2011
     ' http://www.ozgrid.com/forum/showthread.php?t=160528
    Dim lr As Long, r As Long, n As Long, f As String, avg_rt As Long, cf As String 
    Dim c As Range, firstaddress As String 
     
     
    Application.ScreenUpdating = False 
    Columns("E:H").ClearContents 
    Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(5), Unique:=True 
    Range("E1:F1:G1:H1:I1") = [{"ID","Frequency","Average Hours","Avg Amx Hrs","Index Value"}] 
    lr = Cells(Rows.Count, "E").End(xlUp).Row 
    For r = 2 To lr Step 1 
        n = 0 
        f = "=(" 
        cf = "=(" 
        firstaddress = "" 
        With Columns(1) 
             
            Set c = .Find(Cells(r, 5), LookIn:=xlValues, LookAt:=xlWhole) 
            avg_rt = Application.WorksheetFunction.Average(Range("C:C")) 
             
             
            If Not c Is Nothing Then 
                firstaddress = c.Address 
                Do 
                    n = n + 1 
                    f = f & c.Offset(, 1).Value & "+" 
                     
                    Set c = .FindNext(c) 
                Loop While Not c Is Nothing And c.Address  firstaddress 
            End If 
        End With 
        Range("F" & r) = n 
         
        If Right(f, 1) = "+" Then f = Left(f, Len(f) - 1) 
        f = f & ")/" & n 
         
        Range("G" & r).Formula = f 
         
        cf = cf + Str(avg_rt) & "-" & Right(f, Len(f) - 1) & ")*" & n 
         
        Range("H" & r).Formula = avg_rt 
        Range("I" & r).Formula = cf 
         
         
    Next r 
     
     
    Range("G2:G" & lr).NumberFormat = "#,##0.000" 
    Range("H2:H" & lr).NumberFormat = "#,##0.000" 
    Range("I2:I" & lr).NumberFormat = "#,##0.000" 
    Columns("E:H").AutoFit 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Further I'm looking to modify the macro code so that I can perform following tasks.

Step 1: I want to sort all the data in second list on the basis of Avg. hours into ascending order, i.e. the smallest comes first and the highest comes last.
Step 2: Want to calculate the value of (Next Avg. Hours Ė previous Avg. Hours) for each cell up to the last -1 row and want to display under column J
Step 3: Want to calculate the (Avg. Max Hours Ė Avg. Hour) for the last row, i.e. highest with Avg. hours
And display under column J as last row.
Step 3: Under column K want to display the value of (Avg. max hours Ė Column J value)

I hope Iíve clearly explained my requirement, however Iím also trying to achieve this but since being new to VB for me itís a real challenge

Help would be highly appreciated

For reference Iím attaching the work sheet too.

Thanking in anticipation

I have a list with one row per model rocket flight. It has columns for time
of day (AM, PM, Evening), engine type (A, B, C, D etc), how many times that
rocket flew at that time of day and more. When a rocket did not fly at a
particular time of day a row exists with a zero count.

I would like to show, via a PivotChart (and hence a PivotTable) the average
number of flights on each day, regardless of engine type and regardless of
time of day.

If I use the Average sub-total on the number of flights I get an average
that is 3 times too small because the number of data points (rows) for each
day is 3 times too big since there are entries for the morning (AM),
afternoon and evening. I don't want my average to be calculated by dividing
by the number of flights but by the number of days for which I have data.

Can I do this in a PivotTable (and thence into a PivotChart) or should I be
going about this some other way?

I've looked through many postings on averages but can't quite spot what I'm
looking for but then I'm maybe getting confused - PTs do a lot but some of
the calculation nuances need careful thought and I'm probably becoming
fuddled - apologies if I've somehow missed a relevant previous posting.

And many thanks for any pointers you can give.

Kevin Lucas

Hi everyone,

Can =average formula calculate time average. It seems not work with me. I have a list with time format and I want to find the average.

regards,
Sarin

Hello all,

I am having the following problem for which I want to build a macro.

I use a table similar to the one attached which basically has column headers one-hour time slots (such as 06:00:00, 07:00:00 etc) and the cell's contain time values in minutes:seconds. The number of columns is static, however the number of rows is dynamic as it is the output of another macro that returns an unknown number of rows each time.

What I do is taking this amount of data and copy/pasting it into my worksheet under my column headers. Now I want to create a macro that will automatically go to the last row (preferably leaving a couple of rows blank) and doing the following calculations for each column: average, count and another one that I will hardcode in the macro

My problem lies with the dynamic nature of the data, otherwise it would have been easy to create it. Any ideas on how I can work with that?

Please note that the range of columns will be different in my worksheet (i.e it will not be from A->U but probably some other column range, yet static and specified).

Looking forward to your feedback,

Kostas

Hi everyone,

I have huge amount of data with me. I have speeds of vehicles generated at random times(for 24 hrs) in a column. Now I need to divide the data into bins of 300 secs (5 mins) and calculate the average value of speeds for every bin.

The problem I am facing is that the bin size varies from time to time due to randomness in data. So this makes it difficult to the application of a single formula to calculate the average of speeds for vehicles in a particular bin. I can do this manually by seeing the times and calculating the average for every 5 min interval, but it would take a lot of time as the dat is huge.

Can anyone please let me know the procedure to do this?

Thanks
sashi

PS: please let me know if the question is not clear

Hi all,

I'm trying to generate the average time for a number of finishers in a
sailing race. There may be certain boats that have text values in place of a
finish time for did not finish, did not start or did not compete.

Q1 - The spreadsheet I inherited used SUM() to give the sum of the finish
times, COUNTIF(value >0) of the count of the number of finishers, finally to
arrive at average = SUM / COUNTIF. This gives errors as SUM() does not give
the correct value when the text values are present - are there functions
available to give the sum of the race times for the finishers (this figure
also used for other stats)?

Q2 - Does the AVERAGE() function ignore text when calculating the average?

Cheers!
Mark (Skiffie)

Hi All,

I am really having a tough time to calculate average for each new year (it starts with March 1996). I am calculting average for each new year for different companies. Each year starts with the last cell of the last year (for Ist column i.e cell AZ1. So I want to repeat for different years. As the total trading days differ from year to year. only clue is to loop through the starting year date and end year date. On the basis of reference I want to calculate average.

Hope I am clear in my language, if not plz excuse me for that.

I have attached a file for the same.

Thanks and Regards,
Upananda

Hello everyone. I thank you in advance for your time.

I have a weighted average formula which works well (see below).

Packages	Zone
56	2
14	3
21	4
27	5
53	6
106	7
239	8
	
516	6.48255814
That works great.  It does exactly what I want for the chart in question.

The problem I'm running into is if I put a "0" into one of the package columns (see below).

Packages	Zone
0	2
14	3
21	4
27	5
53	6
106	7
239	8
	
460	7.02826087
If I put a zero in the first cell of packages how do I tell the formula not to calcuate it. I need the formula above to not calculate for any zeros in the first column A.

If you have any questions or need more information please let me know.

Thanks

Eddie


No luck finding an answer? You could always try Google.