Average

Closed
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011 - Nov 3, 2011 at 06:25 PM
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011 - Dec 9, 2011 at 09:25 AM
I am using Excel 2007 to find out the average miles driven in a year and the average rate paid for those miles by Employee. However sometimes they are reimbursed multiple times in the same month and sometimes not every month. The data has blank lines and subtotals I need to ignore as you can see below.

Taking employee 100694, they had 1 payment in January, July, August, September and 3 in Febuary. So their AVERAGE Miles per month would be 153.2.

Jan 17
Feb 20 (all 3 added together)
Jul 5
Aug 4
Sept 720
-----
766 / 5 Months = 153.2

I would really appreciate any help you could offer.

A B C D E
3 EMPLOYEE PAY HISTORY REPORT
4 Employee ID Pay Date Amount Rate Miles
5
6 2026 6/10/2011 $42.30 0.47 90
7 Total Transactions: 1 $42.30 90
8
9
10 2098 3/18/2011 $45.90 0.45 102
11 Total Transactions: 1 $45.90 102
12
13
14 2100 4/15/2011 $3.15 0.45 7
15 Total Transactions: 1 $3.15 7
16
17
18 100694 1/21/2011 $7.14 0.42 17
19 100694 2/4/2011 $2.52 0.42 6
20 100694 2/4/2011 $2.10 0.42 5
21 100694 2/18/2011 $3.78 0.42 9
22 100694 7/22/2011 $2.35 0.47 5
23 100694 8/5/2011 $1.88 0.47 4
24 100694 9/30/2011 $338.40 0.47 720
25 Total Transactions: 7 $358.17 766
26
27
28 101382 2/18/2011 $6.72 0.42 16
29 101382 3/4/2011 $10.08 0.42 24
30 101382 3/18/2011 $9.00 0.45 20


9 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 4, 2011 at 05:48 AM
please post a truncated sheet in a workbooo(file) in speedyshare.com or hotfile.com and inform the web address. also explain again how you want the sheet in another sheet of the file
0
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011
Nov 5, 2011 at 08:12 PM
The file is located at http://speedy.sh/8VkAe/Mileage-Average.xlsx

Thanks
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 8, 2011 at 03:48 AM
plase explain(in detail) how you arrived at the vaue of 153.20 and .45 for the employee id of 100694
in the result sheet

the macro is ready but these two values differ.

100694 1/21/2011 $7.14 0.42 17 month 1 AV VALUE 17
100694 2/4/2011 $2.52 0.42 6 month2/AVVLAUE NEXT 3 IS 6.67
100694 2/4/2011 $2.10 0.42 5 month2
100694 2/18/2011 $3.78 0.42 9 month 2
100694 7/22/2011 $2.35 0.47 5 month 7 AV VALUE IS 5
100694 8/5/2011 $1.88 0.47 4 month 8 AV VALUE IS 4
100694 9/30/2011 $338.40 0.47 720 month 9 AV VALUE 720

there are 5 unique months
HOW DID YOU GET THESE VALUE FOR BOTH MILES AND REMIBUSESMENT DOLLARS

besides I am having another problem. with such a large difference between them with five numbers less than 20 and one 720 average gives a wrong representation of the data.
0
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011
Nov 18, 2011 at 07:53 AM
Sorry for the delay in getting back to you. Thanks for looking at it.

153.20 - There are 7 entries but 3 of the entries are in the same month. Therefore the aveage is caluclated as 358.17 divided by 5 months or 153.20. It is NOT divided by the number of entries or 7. Same applies to the .45.
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 19, 2011 at 12:35 AM
I think my mind is not working this morning.

I am sending extract
100694 1/21/2011 $7.14 0.42 17
100694 2/4/2011 $2.52 0.42 6
100694 2/4/2011 $2.10 0.42 5
100694 2/18/2011 $3.78 0.42 9
100694 7/22/2011 $2.35 0.47 5
100694 8/5/2011 $1.88 0.47 4
100694 9/30/2011 $338.40 0.47 720
Total Transactions: 7 $358.17 766

now 358.17 is the sum of that column C for that no. 100694
do you want me to divide by 5 which gives 71.63

what you seemed to have done you have summed up colummn E (which is C column/D column) for each row.
when you add up E you get 766 and divide by 5 you get 153.2


whereas in the second result
Ave $ Reimbursement per Mile per month

what you have done you summed up column D which comes to 3.19 and this time divided by 7 you get .45. why divide by 7 instead of 5 here


now you please clear my doubts.
0
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011
Nov 21, 2011 at 04:46 PM
The 7 lines of data represents only 5 months - so column C's subtotal of 359.17 would be divided by 5 for this employee. Same thing applies to column E. The monthly average of dollar per mile or column D then becomes the average of column C divided by the average of E.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 21, 2011 at 07:52 PM
"C's subtotal of 359.17 would be divided by 5 "

what do you get if you divide 359.17 by 5
0
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011
Nov 22, 2011 at 11:02 AM
$71.63 - I screwed up because 153.20 is the aveage mileage not the average dollar amount. 766 miles divided by 5 = 153.2 MILES. I need to have the monthly aveage of the amount paid (col C), the reimbusement amount (col D) and the number of miles driven (col E). Sorry I messed up.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 23, 2011 at 02:12 AM
I am sending back the file. full name is (given for my purpose)
"tjones full file main macroxlsm.xlsm" download this from this web page

http://speedy.sh/7pFZ6/tjones-full-file-main-macroxlsm.xlsm

you are seeing the file after running the macro
save this file

if you want to retests just run the "A_MAIN_MACARO" again.

when you open the file you may see the option button or other wise a pop up message for enabling macro. if there are not close and again open the button will come up

the only macro you have to run is A_MAIN_MACRO.
BUT ALL THE MACROS MUST BE AVAILABLE IN THE MODULE.
It may take a few seconds for the macro to run. when it is over a msbox "macro over" will come up and sheet "result" will be active sheet so that you can see the results.

any problem clearly point out .

I am repeating the macros also

Dim emp As Range, cemp As Range, rdata As Range, j As Long, rowscount As Long
Dim k As Long, filt As Range, r As Range
Dim cfind As Range
Dim empid As Long
Dim aveage_of_the_amount_paid_col_C As Double
Dim reimbusement_amount_col_D As Double
Dim number_of_miles_driven_col_E As Double

Sub A_MAIN_MACRO()
Application.ScreenUpdating = False
undo
unqemp
Worksheets("mileage").Activate

Set emp = Range(Range("M2"), Range("M2").End(xlDown))
Set rdata = Range(Range("A2"), Cells(Rows.Count, "E").End(xlUp))
For Each cemp In emp
rowscount = 0
rdata.AutoFilter field:=1, Criteria1:=cemp.Value
Set filt = rdata.SpecialCells(xlCellTypeVisible)

filt.Copy Worksheets("filter").Range("A1")
ffilter
Worksheets("filter").Cells.Clear

rdata.AutoFilter
    
Next cemp
Range("J1:M1").EntireColumn.Delete
Worksheets("result").Activate
MsgBox "mcro over"
Application.ScreenUpdating = True
End Sub



Sub unqemp()
Worksheets("mileage").Activate
Set emp = Range(Range("A2"), Cells(Rows.Count, "A").End(xlUp))
 Range("a2").Copy
 Range("J1").PasteSpecial
 Range("M1").PasteSpecial
Range("J2") = ">0"

emp.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J1:J2"), CopyToRange:=Range("M1"), Unique:=True
Range(Range("M1"), Range("M1").End(xlDown)).Copy Worksheets("result").Range("A1")

End Sub



Sub ffilter()
Dim miles As Long, n As Long, m As Long, mmiles As Range
With Worksheets("filter")
'empid = Range("a2").Value
rowscount = .Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
For k = 2 To rowscount
.Cells(k, "F") = Month(.Cells(k, "B"))
Next k
'Range("g1").EntireRow.Cells.Clear
'Range("G1") = "distinct monthst"
For k = 2 To rowscount
''msgbox k
If .Cells(k + 1, "F") <> .Cells(k, "F") Then
n = n + 1
   ' .Cells(k, "G") = n
      m = m + 1
    Else
     miles = 0
     'n = 0
      For m = k To rowscount
        
       ' 'msgbox miles
miles = miles + .Cells(m, "E")
' n = n + 1
' 'msgbox miles
 If .Cells(m + 1, "F") <> .Cells(m, "F") Then Exit For
 Next m
 n = n + 1
 ' 'msgbox m
  ''msgbox k
'.Cells(k, "G") = n
k = m

''msgbox k
End If
Next k

'msgbox n
empid = .Range("a2")
aveage_of_the_amount_paid_col_C = WorksheetFunction.Sum(.Range("C1").EntireColumn) / n
reimbusement_amount_col_D = WorksheetFunction.Sum(.Range("E1").EntireColumn) / n
number_of_miles_driven_col_E = WorksheetFunction.Sum(.Range("d1").EntireColumn) / n



With Worksheets("result")
Set cfind = .Columns("A:A").Find(what:=empid, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Offset(0, 1) = aveage_of_the_amount_paid_col_C
cfind.Offset(0, 2) = reimbusement_amount_col_D
cfind.Offset(0, 3) = number_of_miles_driven_col_E
End If
End With
.Cells.Clear

End With

End Sub


Sub undo()
With Worksheets("result")
.Cells.Clear
End With

End Sub
0
Tjones4852 Posts 11 Registration date Monday May 10, 2010 Status Member Last seen December 9, 2011
Dec 9, 2011 at 09:25 AM
Thanks so much - I will give a run!!!
0