Free Microsoft Excel 2013 Quick Reference

- Average Time Not Calculating
- Calculate average time between button clicks
- Calculate average time between Sales...
- How do you calculate average time?
- Conditional Average Of Range
- Macrco to calculate monthly averages
- Userform - Object not found error then excel crashes on save
- Variable range averages
- Percentage Not Calculating Correctly
- Graphing Data - 'Data Entry' YTD Average Column
- Automatic Average Calculation
- Average of times where text may be in range
- Macro for sorting data and performing specific calculations.
- PivotTable day-average from data that's sub-day
- calculate average time
- Macro for Average/Count/other calculation on dynamic size of worksheet
- Dividing data into bins and calculating the averages
- Average of times where text may be in range
- Calculating a function on the basis of cell reference
- Excel weighted average question

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

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

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.

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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:As of right now, this code is producing monthly sums but not calculating the average.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

It seems so simple but I can't figure it out. Can somebody please help? Thanks!

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.

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

=((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 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?

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

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)

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 SubIf 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

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

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

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

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

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)

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

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.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.

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 you have any questions or need more information please let me know.

Thanks

Eddie

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