Id like to calculate TOTAL COST based on user entering mileage into one cell:
First 2 miles of journey are charged at £3.50, additional miles thereafter ( on the same journey) are charged at £1.50. Any ideas?
Thanks in advance
Id like to calculate TOTAL COST based on user entering mileage into one cell:
First 2 miles of journey are charged at £3.50, additional miles thereafter ( on the same journey) are charged at £1.50. Any ideas?
Thanks in advance
Hello & Welcome to the Forum,
Maybe just...
=(2*3.5)+((A1-2)*1.5)
HTH
Regards, Jeff
i think wrap it with below considering 1 or 2 km as well
=IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5))
Click on * below if you find this helpful
Thanks,
A
So we get all the requirements correct the first time...
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Thanks for asking jeff, think amy_d2 has got it, except for one niggle, when cell is at 0 fr mileage the cost is being calculated at
You could just say less than 3...
...and check A1 for a number...
=IF(A1,IF(A1<3,A1*3.5,(2*3.5)+((A1-2)*1.5)),"")
£3.32, but when changed to 1 mile calculates correcty as 3.50?
Thats true too, will give it a try!
Hi You can use this
=IF(A1=0,"",IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5)))
should have consider 0 too , sorry about that
Edited
or use jeffreybrown's formula
Last edited by amy_d2; 08-16-2013 at 12:21 PM.
That works, and bit neater looking, thank you
Amy, Jeff,
If I wanted to slightly change the calculation, can you please help?
I want the first 2 miles to be charged at £3.50 inclusive; for example regardless if they travel 1/2 mile, 1 mile, 1.5 miles or 2 miles, the charge is £3.50. Once that threshold is reached, anything in excess of 2 miles is charged an additional 1.67 per mile
Therefore a passenger travelling 1 mile charge = £3.50, 2 miles = £3.50, 3 miles = £4.67, 4 miles = £6.84
Thanks in advance
Amy, Jeff,
If I wanted to slightly change the calculation, can you please help?
I want the first 2 miles to be charged at £3.50 inclusive; for example regardless if they travel 1/2 mile, 1 mile, 1.5 miles or 2 miles, the charge is £3.50. Once that threshold is reached, anything in excess of 2 miles is charged an additional 1.67 per mile
Therefore a passenger travelling 1 mile charge = £3.50, 2 miles = £3.50, 3 miles = £4.67, 4 miles = £6.84
Thanks in advance,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks