Formula for HMRC Mileage rates

[Jinky_uk]

This will have been done to the death but i'm really struggling, having tried all sorts of nested IFs and SUMs!

I have 12 worksheets, one for each month of the financial year, with each column M being mileage for individual trips and I want each column N to show the reimbursable amount according to HMRC rates which change at 10,000 miles. There is another sheet named 'Overview' that has the <10,000 mile rate in J4 and the >10,000 mile rate in J5. There is a cumulative mileage in G18 on this sheet which pulls every individual trip into one cumulative mileage.

I keep either returning the >10,000 rate for EVERYTHING when G18 reaches 10,000, or i end up with column N returning a figure if column M is empty (i don't want to have to populate M with zeros as there is risk of incorrect results if a zero goes missing, and it won't me me using the workbook most of the time).

Can anyone help?

[BB1972]

Jinky, welcome to the forum. You should upload a workbook, showing a small sample of your data, and also showing the expected result. I'm sure then we can help you with your query.

[Jinky_uk]

Thanks BB1972, hadn't thought of that!

Column N on April 2013 is where i want it to calculate mileage amount for that trip, taking into account whether the annual mileage on the Overview sheet is over the 10,000 rate threshold, without it all recalculating at the lower rate!