Odometer to Total Mileage in a Period 1

[elbergeron]

OK, I can't figure this out, help please

Employees enter their end of day odometer reading on their timesheets which I transfer to a spreadsheet as follows:

Sun a1 0
Mon a2 160780
Tue a3 160805
Wed a4 0
Thu a5 160902
Fri a6 0
Sat a7 161244

Total Km = 464 (161244-160780)

What would the forula be to come to the total of 464 when some of the days are zero? The employee could work all seven days or only 1 day.(magic answer please)

 

[mrsnrub]

I'm not sure your total km logic is correct. I believe you really need to have a km value for the end of the previous week. By your current logic, the km's travelled on Monday is 0. And any formula you write using this logic will return 0 if the employee only works 1 day.

That said, given your example you could use:

In cell A8 enter the array formula to give you the minimum value greater than 0:
=MIN(IF(A1:A7>0,A1:A7))
Note, to enter an array formula, you must press Ctrl+Shift+Enter.

In cell A9 enter for your final result:
=MAX(A1:A7)-A8

Hope this helps.

 

[SkipVought]

Hi,

You need to accumulate your data in one table in one sheet, week after week in the same columns.
[tt]
Dte Odometer
2007/06/29 27,287
2007/07/02 27,346
2007/07/03 27,374
2007/07/04 27,396
2007/07/05 27,409
2007/07/06 27,462
[/tt]
Therefore, the distance for the week is, using named ranges...
[tt]
D2 (WeekBeginning): 2007/07/02
D3: =INDEX(Odometer,Match(WeekBeginning+7,Dte,0),1)-
INDEX(Odometer,Match(WeekBeginning,Dte,0),1)
[/tt]


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[elbergeron]

Thank you to mrsnrub, exactly what the doctor ordered!