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!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Looking up dates and splitting a travel cost evenly
0
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!
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
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.