# Calculate average \$/HR by pulling data from two workbooks

Closed
chouston Posts 1 Registration date Monday March 25, 2013 Status Member Last seen March 25, 2013 - Mar 25, 2013 at 05:41 PM
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 - Apr 29, 2013 at 07:47 AM
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:

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 response

sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Apr 29, 2013 at 07:47 AM
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