# Macro Loop – Find Specific Times, Extract Date, Average Values Of Times

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
ADVERTISEMENT
Jan 24, 2014

I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.

Example below: (Find value = 1 in A:A) ... 1 is found 4 times.

A | B

1 | 295

3 | 123

1 | 400

5 | 425

1 | 354

1 | 400

In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370

View 6 Replies
View Related
Apr 8, 2014

I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:

E3 provides the start time of 4:00

H3 provides an end time of 15:30

If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.

View 7 Replies
View Related
Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

View 13 Replies
View Related
Apr 19, 2014

I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.

I need to be able to repeat a row of data a specified amount of times.

Example:

3 Yellow James

2 Green Mark

5 Purple Sue

1 Orange David

Needs to look like:

3 Yellow James

3 Yellow James

3 Yellow James

2 Green Mark

2 Green Mark

5 Purple Sue

5 Purple Sue

5 Purple Sue

5 Purple Sue

5 Purple Sue

1 Orange David

View 7 Replies
View Related
May 5, 2008

I will say exactly what i want.

I want to keep statistics of roulette.

More specifically, i want to count how many times a dozen lates to come.

The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.

I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.

For example: I have put in a column 500 numbers from 0 to 36.

The output must be like this:

1st Dozen: Every 1 time: x

Every 2 times: n

Every 20 times: m

The same for 2nd and 3rd dozen.

View 9 Replies
View Related
Aug 22, 2008

I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.

However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.

View 9 Replies
View Related
Mar 11, 2013

I have the following string and I can't figure out how to extract the day and the times so it would be in columns

Day | Time | Time | Day | Time | Time

Sunday9:00am-11:00pmMonday8:00am-11:00pmTuesday8:00am-11:00pm

View 8 Replies
View Related
Jan 23, 2008

This sheet has several years of data so is full at 65,000 rows.

One issue is that there may be skip or gaps in the date sequence, so I can't rely on the relative position of cells to be accurate.

Thanks for any ideas!

******** ******************** ************************************************************************>Microsoft Excel - solar1.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD6492810/1/200313:00:00308 6492910/1/200313:15:00302 6493010/1/200313:30:00280 6493110/1/200313:45:00323 6493210/1/200314:00:00366 6493310/1/200314:15:00317 6493410/1/200314:30:00247 6493510/1/200314:45:00219 6493610/1/200315:00:00259 6493710/1/200315:15:00259 6493810/1/200315:30:00233 solar1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Jun 9, 2014

If you have some low time resolution data you need to compare with high time resolution data how do you average the high resolution data. For example data that starts and ends a fortnight apart and you want to compare it with data that starts and ends 1 hour apart you need to average the hourly data to do so. I can't figure out how:

So if I had Sheet 1 with hourly data

A B C D E

1 Date, Time, Measurement

2 01/01/2014, 01:00, 62

3 01/01/2014, 02:00, 38

etc....

Then in Sheet 2, fortnightly data

A B C D E

1 Date on, Time On, Date Off, Time Off, Measurement

2 02/01/14, 15:23, 18/01/2014, 12:25, 52

3 18/01/2014, 12:25, 01/02/2014, 14:19, 34

etc....

I'd want to add a new column of data to average the hourly data so I can use the correl function.

View 7 Replies
View Related
Mar 18, 2009

I am trying to get an average of delivery times.

On Monday's I have time's of:

View 6 Replies
View Related
Apr 3, 2008

How can you find the average of times?

I have times like

00:01:00

00:00:15

00:00:08

All are in dd:hh:mm format

I need to find their average which shld be 00:00:28 (approx 27.6 mins)

When I do average(A1:C1) its giving me div/0 error.

View 11 Replies
View Related
Jan 29, 2013

I am measuring elapsed time for an event ie: began at 00:29 in ColA, ended 00:34 in ColB (or began 12:29 AM, ended 12:34 AM) for an elapsed time of 5 minutes. My result is being displayed as 00:05 in ColC which seems perfect at 0 hours and 5 minutes, but excel is seeing the result as 12:05 AM.

I have 20 rows of data and trying to get an average of the elapsed times in ColC

How do I get excel to read this as 0 hours 5 minutes instead of as the time of day?

View 7 Replies
View Related
Jul 8, 2006

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.

View 7 Replies
View Related
Jan 10, 2008

I work at a call center, and I need to find the average duration of time our employees spend on the phone. Here are some of the call times:

0:00:00

0:00:01

0:00:02

0:00:03

0:00:04

0:00:05.......

How would I find the average call time of these calls? When I use the formula =AVERAGE(Num1:Num2) I receive a # DIV/0 error.

View 3 Replies
View Related
Apr 25, 2008

I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January.

Percentage of Capacity By Hour Hour Month/Day/Year

0.00% 11:00 1/22/2007

0.00% 12:00 1/22/2007

0.00% 13:00 1/22/2007

0.00% 14:00 1/22/2007

0.00% 15:00 1/22/2007

0.00% 16:00 1/22/2007

0.00% 17:00 1/22/2007

0.00% 18:00 1/22/2007

0.00% 19:00 1/22/2007

0.00% 20:00 1/22/2007

0.76% 21:00 1/22/2007

1.59% 22:00 1/22/2007

1.38% 23:00 1/22/2007

2.68% 0:00 1/23/2007

1.75% 1:00 1/23/2007

3.65% 2:00 1/23/2007

3.40% 3:00 1/23/2007

2.41% 4:00 1/23/2007

View 5 Replies
View Related
May 22, 2009

I want to copy range B2:B5 value only and paste it 19 times from B12:B15, B17:B20, B22:B25, and on. The distance between any two pasted range is tow rows.

View 9 Replies
View Related
Mar 26, 2014

I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:

Code:

A

B

C

D

E

F

Category

Start

End

Hours

Minutes

Seconds

Transportation

2013/01/01 01:00:00

2013/01/01 01:15:00

0:15:00

15:00

900

[Code] ...........

I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':

Code:

A

B

C

D

E

F

Month

Category

Occurrence

Hours

Minutes

Seconds

2013/01

Transportation

48

[Code] .......

I have figured out a way to count the occurrences for Transportation for January:

=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"

View 1 Replies
View Related
Oct 11, 2006

Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

View 9 Replies
View Related
Mar 7, 2009

I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,

Column D Column E Column F Column G

Start End Lunch Total Hours Worked

10:01AM 7:08PM 1:01 (formula to convert hours worked)

View 9 Replies
View Related
Nov 18, 2009

I've attached below a small part of my code. I am wondering if there is a way to do this, without copying and pasting something 30 times, but using a loop instead. As you can see there are a series of productsNumbers (30 in total, but this example is for 3). Each productNumber is a string that is actually a 5 digit number, so it can be an integer as well, if it has to.

I would like to code below to loop for as many products as there may be. The way that I have it right now does not work - I feel that I am missing something small or that there is no way to do this.

View 3 Replies
View Related
Apr 13, 2008

I have a routine that loops through a block of records containing two time fields. One is the same for every record (let's call it the start time) the other, which varies, is the last time at which an event happened before or after the Start time (let's call it the Event time). so a pseudo block of data may look like this;

A B

15:00:00 15:01:27

15:00:00 15:00:05

15:00:00 14:59:27

15:00:00 14:59:01

15:00:00 14:48:45

I need to identify the record that has the closest time in column B to that in column A (in the example above that would be B2 (15:00:05). I then save that record off and eliminate the rest for that time block. The whole subroutine then repeats for the next block of data and so on down the list, top to bottom ( times are in descending order). Note also that times will never span the 24-hour point - they all occur betrween 12:00 and 22:00 on any given day.......

View 2 Replies
View Related
Apr 28, 2014

I am looking for a way to calculate the average time, over a number of times.

So basically, would have different times in 24 hour hh:mm:ss, all in column i.

I have tried =AVERAGE(i2:i18) and it doesn't work.

I have also tries Ctrl/Shift enter as an array, it didn't work.

I tries to format cells as time and number, it didn't work!

View 1 Replies
View Related
Jan 5, 2012

I have a sheet set up with date in Column A, time in column C and numbers in column D, starting in row 2 down. I made a pivot table with the dates grouped by day, 7, to show a week and I can group the time by hours, 6am, 7am, 8am. but what I want to do is group the time for times between 2 times, like from 6:00 AM to 10:00 AM, 10:01 AM to 2:00 PM and 2:01 PM to 8:00 PM, what I am wanting to do is group by week, group by the times above and average the numbers for that time. Is there anyway to do this in a pivot table? Or some other way? this is for use in Excel 2003

View 2 Replies
View Related
Aug 20, 2013

I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.

I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.

View 9 Replies
View Related
Feb 8, 2010

I was playing aroung some VBA code, but the code executes 98 times and I don't know why. For example, range A1 contains 5. When I type, say 3, in A1, I want A1 to show me 8 (5+3). Then I may type 10, in this case A1 must show 18 (8+10). And so on. I decided to place entered values in different column, then sum them in A1 with below stated code

View 3 Replies
View Related
Aug 9, 2006

I have a sheet with data in rows across several columns.

I want to copy the data from each row into a new sheet and save it with another name (which I already have ascript for), but as I have 10 rows , basically all I've done is copy the script 10 times and chaged the row numbers and nae to save as.

What I want is one script that does all I have now, but then loops back to do the next row down and changes the name to save it as as well (which is the person's name listed in the row)

View 9 Replies
View Related
Jul 29, 2013

I have 20 numbers from D3 to W3. Numbers range from 1 to 80. There are several rows with numbers.

I would like a macro to pic from each row specific numbers and copy them to EC3.

View 8 Replies
View Related
Sep 30, 2013

I have a rand () which provides me a different number upon calculate. I would like to run a loop for 1000 times and take the value and copy and paste in a range.

The below sorta works, but at times it stops or doesn't work at all. I have a loop for the counter and the for each to place the value into the cell and have it run over and over and placing the value in the next cell underneath. I have the counter at 10 so I dont get stuck in some endless loop before I know it works.

Code:

Sub montecarlos()

Dim MCs As Long

Dim c As Range

Dim lCount As Long

Dim lNum As Long

lCount = 0

lNum = 1

Worksheets("MonteCarlo").Activate

[code].....

View 6 Replies
View Related
Mar 2, 2007

I am coding a macro that would take all information needed from an existing pivot to put in in a new report file.

Now the problem is that I couldn t get to paste the data (for some weird reason), so I did a loop that terminates with IsEmpty...

My problem is I have to write on loop per column, because I don t know how to tell excel to go to next column (that I know!) AND to the first row, which means doing an offset of (x,1), but how do you set the x for it to be adaptable if I change the length of the pivot table?

I really hope someone can help me with this, I tried to replace range("first cell") by cell(x,... and set x as a variable but i am not good enough obviously....

Here is what I have so far:

Dim k

Dim w As Integer

Workbooks.Add

ActiveWorkbook.SaveAs "C:Dokumente und EinstellungenCustomerServiceDesktopMonthlyReportAutomated.xls"

'iniatiation before loop

Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Activate

Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Range("a20").Select

k = Selection

View 9 Replies
View Related