Logic Expressions in MS Excel!

Factual Questions
1

Hey all,

I would really hate for people to think that I only ever post here when I need help, but here I am in a bit of a quandry.

I’m trying to put together an Excel document for my company that will simplify the tracking, logging and payment of vehicle allowances on a daily, monthly and yearly basis.

I have a document that works in pretty much every way, and will give monthly KM totals as well as a correct yearly $ total at the very end.

My biggest issue is in getting the monthly $ totals to work out correctly. The way revenue Canada calculates a ‘fair’ allowance is as follows:

The first 5000km are given $0.52 per km.

Any km after that are given $0.42 per km.

My issue is that I can’t seem to create a logic expression that will check my yearly km total (which is always correct, up to date and in the same cell every time), then calculate a monthly $ total that factors in whether to do it at the $0.52 rate or the $0.42 rate. I’ve tried doing it a handful of different ways and none seem to give me the correct numbers.

It’s driving me to distraction, so I’m posing the issue to you folks. Hopefully one of you Excel guru’s can help me out.

For further convenience, I’ve uploaded the Excel document in question at the link below. The file-sharing service is perfectly safe and I’ve used it for years to share files with more than one person.

Theres no real rush on this; this is something Im putting together mostly for my benefit as well as for future employees. It would be nice to have a working document though.

Thanks in advance; this board hasn`t failed me yet!

  • Budista
2

=if(A1<=50000, A1*.52, 26000+(A1-50000)*.42))

3

Right…

Looking at that formula, my only issue with that is that it seems to refer only to that particular MONTH, rather than the yearly total. The issue I`m having concerns the fact that I need the monthly $ total cells to consider how much there is in the YEARLY KM total cell before applying one of the two rates.

If Im totally misunderstanding what you wrote, please let me know. Im not really much of a math person.

  • Budista
4

Budista,

So the rate applies to the first 5,000 km per month or annually?
I’m thinking that it’s annually from your most recent comment, but I want to make sure I understand.
And what you’re looking for is something that checks a rolling total and says once that exceeds 5,000 switch to the different amount for the rest of that month and every subsequent month, is that right?

5

This is exactly what I’m looking for. I just have no idea how to format that into a cell.

The way RC calculates it is that kilometers 1-5000 are at $0.52 apiece, 5001 onwards are at $0.42. This is calculated annually, not montly.

6

Ah - I just downloaded the doc. Mine isn’t correct (and I used 50,000 instead of 5,000…).

I would completely rework the spreadsheet:



Date       Start     End       Mileage      Cum. Mileage   Reimbursement
1/1/2010   0         100       =C2-B2       =D2            =IF(E2<=5000,D2*0.52,(5000-E1)*0.52+(E2-5000)*0.42)
1/2/2010   100       1000      =C3-B3       =E2+D3         =IF(E2>5000, D3*0.42, IF(E3<=5000, D3*0.52, (5000-E2)*0.52+(E3-5000)*0.42))
1/3/2010   1000      7000      =C4-B4       =E3+D4         =IF(E3>5000, D4*0.42, IF(E4<=5000, D4*0.52, (5000-E3)*0.52+(E4-5000)*0.42))
7

To clarify the above:

The first line is always a dummy line - you can’t check against anything above it, obviously.
Beyond that, you can just drag the formulas down. The Reimbursement checks to see if the previous line hit 5000 km. If it did, then it’s that day’s mileage at $0.42. If not, then it calculates the mileage up to and including 5000 km at $0.52, then the rest at $0.42. I’m sure you could do a pivot to add all the dates up monthly.

8

Budista,

Do you need monthly break downs in the reimbursement or just total annual owed?

9

Munch: If the first 5000 km are priced at $0.52, shouldn’t the reimbursement read


=IF(E3>5000, D4*0.42, IF(E4<=5000, D4*0.52, (5000*0.52)+(E4-5000)*0.42))

After all, if cumulative mileage is above 5000 km, then the first 5000 km have to be priced at $0.52, regardless of any other considerations.

10

Munch:

As mentioned, I’m really not a huge math person. That being said, I think Duke might have a point with the first 5000 priced at $0.52. That`s one of the issues I was having with the way I had created it; not being able to tell it to distinguish between the first 5000 and the subsequent.

Jonesj2205:
Actually, I really do need monthly breakdowns. If I didnt, the document I have actually does the yearly totals correctly. The issue is that the reimbursement from the company is done on a monthly basis (1st of every month), so I need those totals on the page. Its not a hard thing to calculate manually, but Im trying to get a simple, elegant spreadsheet thats going to do it all for us. I just don`t know how to factor in the switch of rates based on the running total of km, like you mentioned earlier.

11

No. It’s trying to get that 4000 km from yesterday’s final mileage (1000) to 5000, so that you’ve only charged the first 5000 km total at $0.52, then the rest at $0.42.

12

Badista,

I was afraid you were going to say that. I’m thinking Munch (with Duke’s change) is on the right track.

13

To clarify - Duke’s formula works ONLY if you finish a day with your Cumulative Mileage at 5000. If you don’t, it’s going to calculate the day when you go from under 5000 to over 5000 all at $0.52 (in this case, 1/3/2010).

14

Munch:

Well… from the sounds of things, your formula works great. I’ll try and reformat the spreadsheet exactly as you have it laid out.

I’m pretty new to Excel, so things like this are a bit tricky for me to pick up at the moment.

While I have your attention, is there an easy way to make Excel put in a range of date’s for a given year? Like… Jan 1st through December 31st? I can’t seem to figure that one out either.

Thanks again for your help,

  • Budista
15

That’s an easy one - just select the 1/3/2010 on my sheet, and drag it down to A366. Excel will do it automatically.

Or you could just let the person entering information plug in the date they drive, and leave the rest of the dates alone. No need to add empty rows with no data.

16

Munch:

Thanks again for all your help.

I’m currently redoing the document (I’m going to leave in empty rows of data, just because it’s sort of a nitpick the owner of the company prefers), but wouldn’t mind you just giving it a once over to make sure my formatting changes haven’t totally screwed up the cell relationships.

Mind if I reupload and send you a pm after I’m done?

  • Budista
17

Sure thing - not a problem.

18

Ah–got that now. You are correct, I think.

19

It gets confusing. Here’s an easy way to check - jack up that trouble day to exploit it:

Set Day 1 as 0 to 4,999 km (4,999 total km).
Set Day 2 as 4,999 to 100,004,999 km (100,000,000 total km).

Day 1 charges $0.52 for everything - *juuust *under $2,600 ($2,599.48 to be exact). Day 2 should obviously charge only 1 km at $0.52, and the rest at $0.42, for a total of *juuuust *over $42 million. ($42,000,000.10 to be exact.) Doing that with your formula spits out $42,002,599.58 on Day 2 - meaning it doublecharged for the first 4999 km.