Calculate average $/HR by pulling data from two workbooks

[Closed]
Report
Posts
1
Registration date
Monday March 25, 2013
Status
Member
Last seen
March 25, 2013
-
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
-
Hi, I have been researching trying to figure out how to do some data manipulation with VBA, I have been going nuts trying and failing at making this work, if it's something anyone can help with, I would really appreciate it...

The files I am using can be found here:
https://docs.google.com/file/d/0B8A5F4oHm9H3a09FQV93MkZhbFU/edit?usp=sharing

https://docs.google.com/file/d/0B8A5F4oHm9H3MjJJamJFdFlSTG8/edit?usp=sharing

I have two excel files that I am trying to gather information from to calculate the average $/hr earned by employees. The first file, which is currently called "Book 1" is generated by a different VBA code, the other "Employee Hour Output" is generated by our time clock program. What I am trying to do is have the script run from the PERSONAL workbook and look in the Employee Hour Output. In column B there are Employee names, for each employee that worked, there is a list of invoice numbers located in column E. The code needs to see which invoice numbers each employee worked on then compare that to the $/HR earned from that job in column D of the Book1 workbook. Then based on all the jobs worked, calculate the average $/HR that employee earned in the given time period. This information needs to be displayed with the employee name and $/HR in the first available row of columns H and I of the Book1 workbook.

1 reply

Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
44
HI

Please Try the below formula for average calculation for book1

in I4 write the below formula

I4: =AVERAGEIF($E$2:$E$70,$h4,$D$2:$D$70)

and copy across

and for second thing i can't understand can you elaborate me once again

Thanks

Patnaik