Hi
I'm trying to do a mileage expense formula based on the following (i'm not sure if my attachment will load up)
Km cent
Rate 1 0 - 1500 44.79
Rate 2 1501 - 5500 83.53
Rate 3 5501 - 25000 32.21
Rate 4 25001 - 25.85
Total Mileage to date 9500
Can anyone help with a formula. I'm guessing it's an IF formula rather than a vlookup?
could you explain a bit more as to how your mileage is supposed to work? Is it that for 9500 the first 1500 goes at 44.79 (1500*44.79=67185), then you take the next 3500 (5000-1500) and multiply that by 83.53 and add the 671.85 (3500*83.53=292355) then the rest, 4500 (9500-5000) goes to the 32.21 (which is 144945) and add all three together (504485 cents)?
What should the total be?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi Sambo kid yes that's how it's calculated
you didn't answer what the answer should be for 9500.
That would be a help.
I can do three individual formulas and am getting the same answer as you €5,044.34 (euros rather than cents) but I'm trying to figure out how I do one formula instead of three separate ones and adding them together (much like your calculations).Originally Posted by Sambo kid
Try setting up like this
Then in D14
Dave
This site might help you with tiered calculations.
http://www.mcgimpsey.com/excel/variablerate1.html
Dave, glad you got it, I've been playing with it for over an hour but not getting it with sumproduct. I was going to set up a column like you did in E but the negatives kept messing with my head.
Kudos to you.
Last edited by Sam Capricci; 11-19-2019 at 07:21 PM.
FlameRetired, glad you got it, I've been playing with it for over an hour but not getting it with sumproduct. I was going to set up a column like you did in E but the negatives kept messing with my head.
Kudos to you.
Guys thanks so much for your help. I’m going to have to sit down and digest that and thanks for the link I feel I’m a bit out of my depth
LOL. Me, too. At first glance it makes no sense ... but then gradually.Dave, glad you got it, I've been playing with it for over an hour but not getting it with sumproduct. I was going to set up a column like you did in E but the negatives kept messing with my head.
Kudos to you.
Oh yes; thanks for the Kudos, feedback and rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks