Looking up dates and splitting a travel cost evenly

0

Hi,

I want a formula that will look up dates and split a travel cost evenly for contractors.

For example:

If they work on two jobs on one day, I want to split the daily travel costs evenly.

1/1/2018 Job A 8 hours
2/1/2018 Job A 4 hours
2/1/2018 Job B 4 hours
3/2/2018 Job C 8 hours
4/2/2018 Job C 2 hours
4/2/2018 Job D 6 hours

Daily travel: $20.50

So what formula do I use to lookup the dates and split the travel so it will give me the following:

1/1/2018 Job A 8 hours $20.50
2/1/2018 Job A 4 hours $10.25
2/1/2018 Job B 4 hours $20.00
3/2/2018 Job C 8 hours $20.00
4/2/2018 Job C 2 hours $5.125
4/2/2018 Job D 6 hours $15.375

Thank-you for any help!


Answer
Discuss

Discussion

I tried a VLOOKUP function and listed the split amount for each hour, but I want it to be able to check if there are duplicates for the date and then split.  Otherwise, I want to give it the full amount for the daily travel costs:

For example:
1/1/2018 Job A 8 hours $20.50
2/1/2018 Job A 4 hours $10.25
2/1/2018 Job B 4 hours $20.00
3/2/2018 Job C 8 hours $20.00
4/2/2018 Job C 2 hours $5.125
4/2/2018 Job D 6 hours $15.375
5/2/2018 Job D 6 hours $20.50  (as there are no other jobs for that day)

Thanks again!
Zippoah (rep: 2) Apr 10, '18 at 2:11 am
Add to Discussion

Answers

0
Selected Answer

Unfortunately, you didn't provide a layout of your data. Therefore I presumed dates in A2:A7, Jobs in B2:B7 and Hours in C2:C7.

You also didn't provide a design for the result data. Therefore I created one. I placed the jobs A to D in E1:H1, then I placed the following formula in E2 and copied to E2:H7.

=IF(E$1=$B2,20.5/COUNTIF($A$2:$A$7,$A2),"")

The formula simply devides $20.50 into the number of occurrences  of each date. You may be able to adapt the cell references to another layout.

Note that I generally oppose the alternative result of "" used in the above formula's IF() component. In my own worksheet I would make that a zero and then format the sheet or cell to hide zeroes. The difference is that "" is not a number and when you try to use it in calculations errors will occur. The only exception to that rule is Excel's SUM() function which can interpret "" as 0.  In the example I created, summing up columns E:H will pose no problem but if you wanted to add 10% service charge, for example, =$D2 * 1.1 would result in an error but =SUM($D2)*1.1 would give the correct result of zero. This looks convoluted and I agree. That's why I wouldn't let the formula enter a string ("") in the first place. The correct result of the formula is zero and therefore the formula shouldn't write something else in the cell. There are several ways to hide zeroes if you don't want them to show. There should be a tutorial on this site somewhere if you need advice - or ask another question.

Discuss

Discussion

Excellent, thank-you so much!!
Zippoah (rep: 2) Apr 10, '18 at 10:07 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login