Calculating daily mileage payment

Hi guys I have a sheet that I would like to calculate my daily travel allowance.

these are the required criteria

1 only one payment per day
2 the payment is for the highest mileage for the day
3 my day schedule is erratic at best so a pre defined lookup wont work
4 I may or may not travel during my working day so I dont get any travel time for that if I do

I have attached a sample sheet to show how it all works.

I looked into this some months ago and thought I had it sussed with the forums help but it wasnt quite right and I have not had time to get back to it since.

In the sample for Mon the highest trip for the day is travel home 110 miles so i would get £32.14 and ignore the other totals.

As you can see each day has a different amount of jobs and situations.
I can create new cells off sheet to accommodate any extra calcs.

many thanks in advance

theres proably a better way to do this
But I would use
=IF(OR(A5="",A5=A6),"",MAX(IF($A$5:$A$32=A5,$H$5:$H$32)))
as an array formula
so use
Control + Shift + Enter

I have added that into the cell to return the mileage max in the payment section - so i know its wrong for the title = but shows the correct max mileage calc

so then you need to lookup that mileage to get the amount to be paid - correct

not sure how the time section and miles work the value

over 70+ miles if journey >90mins 5.89 for every 20miles

where is this stored ?

Hi
Thanks for taking the time to reply, I thought I had posted a reply the next day but seemingly not. :-(

Ok this sort of works.
The figure in Column U needs to be the payment £value from the Travel Allowances in the case of 110 it would be £19.67 Tab (it is not upto date but sufficient for now)
I tried moving your formula in another column but it doesnt work, it just gives me a total for the day.
then I tried making another lookup column from the Travel allowances tab and that went horribly wrong too..

I did notice one issue with the formula, If I travel to a site and then travel to another site and back again and then home and the mileage in the middle is greater than the to work and to home I get the higher middle mileage.

B5 travel to site =35
B6 mr A
B7 Travel to collect equip=70
b8 travel back to site=70
b9 mr A
B10 Travel Home=35

the payment for the day should be £11.39 as we can only claim to and from work.

I can put specific text into the to work and from work column if that helps.

We can use as many off sheet columns as required.

Many thanks in advance.
Keith

i think we need to use a helper column , i cant work out a way to do the OR - home/work - someone else hopefully will

so I have put a simple IF statement in column AB
=IF(OR(E5="work",E5="home"),H5,"")

The figure in Column U needs to be the payment £value from the Travel Allowances in the case of 110 it would be £19.67 Tab (it is not upto date but sufficient for now)

Yes, as i said

I have added that into the cell to return the mileage max in the payment section - so i know its wrong for the title = but shows the correct max mileage calc

but i was not sure how your rules apply

if you can explain the rules for the payment , we maybe able to setup a lookup to return the £ value into that cell

I tried moving your formula in another column but it doesnt work, it just gives me a total for the day.

its an array formula - so if copied anywhere - you need to use
control+shift+enter to get {} around the formula

Hi thankyou for the continued support.
It is starting to come together

I have finally moved the formula and sorted the array :-)

right what i think I need is a lookup. I tried this
=LOOKUP(AC5,TravelAllowances!A1:A389,TravelAllowances!B1:B389)
but in cells with a blank value I get $N/A however the populated cells give me the correct monetary value.
I.e. 15 miles = £2.85 27 miles = £5.69 etc from the values in TravelAllowances sheet
Obviously Column A1 to A400 are the miles and B1 to B400 are the payments for the given miles

regards

Keith

lookup needs the table sorted - you may want to try vlookup

to avoid the error on blank cells you have a few choices

IFERROR( LOOKUP(AC5,TravelAllowances!A1:A389,TravelAllowances!B1:B389), "")
which will return a blank when the lookup returns a #n/a

or

IF( AC5="", "", LOOKUP(AC5,TravelAllowances!A1:A389,TravelAllowances!B1:B389))

you did mention that there was another rule to the mileage which changed a simple lookup working

over 70+ miles if journey >90mins 5.89 for every 20miles

which is why i wanted to fully understand the rules

Hi,
Thankyou for all your efforts and patience, you have cracked it, it works like a dream.
The mileage over 70+ miles if journey >90mins 5.89 for every 20miles I ended up just amending the Travel allowance sheet to incorporate that info.

I have just completed this weeks timesheet and it worked like a dream as long as i remember to populate Column E, I even managed to go as far as using conditional formatting to hide the work home info in Column E so it is all great

many many thanks

Keith

excellent :up: