# Logic Expressions in MS Excel! **URL:** \ **Category:** Factual Questions\ **Created:** [February 17, 2010, 6:15pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369 "2010-02-17T18:15:11Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 6:15pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/1 "2010-02-17T18:15:11Z")
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. > **[Hightail Spaces](https://spaces.hightail.com/resolve/download/RmNES3d1K3h5UkYzZUE9PQ)** > > Hightail Spaces lets you send unlimited files, of any size, to anyone. Collaborate with your team and evolve your work. There`s no real rush on this; this is something I`m 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 ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 6:33pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/2 "2010-02-17T18:33:37Z")
=if(A1\<=50000, A1\*.52, 26000+(A1-50000)\*.42)) ---
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 6:40pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/3 "2010-02-17T18:40:43Z")
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 I`m totally misunderstanding what you wrote, please let me know. I`m not really much of a math person. - Budista ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [February 17, 2010, 6:49pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/4 "2010-02-17T18:49:31Z")
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? ---
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 6:55pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/5 "2010-02-17T18:55:51Z")
> [@jonesj2205](#): > > 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? 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. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 7:00pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/6 "2010-02-17T19:00:10Z")
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: ```auto 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)) ``` ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 7:05pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/7 "2010-02-17T19:05:36Z")
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. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [February 17, 2010, 7:06pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/8 "2010-02-17T19:06:20Z")
Budista, Do you need monthly break downs in the reimbursement or just total annual owed? ---
**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\ **Post date:** [February 17, 2010, 7:06pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/9 "2010-02-17T19:06:22Z")
**Munch** : If the first 5000 km are priced at $0.52, shouldn’t the reimbursement read ```auto =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. ---
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 7:15pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/10 "2010-02-17T19:15:03Z")
**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 didn`t, 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. It`s not a hard thing to calculate manually, but I`m trying to get a simple, elegant spreadsheet that`s 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. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 7:18pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/11 "2010-02-17T19:18:08Z")
> [@Duke](#): > > **Munch** : If the first 5000 km are priced at $0.52, shouldn’t the reimbursement read > > ```auto > > =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. 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. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [February 17, 2010, 7:19pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/12 "2010-02-17T19:19:09Z")
Badista, I was afraid you were going to say that. I’m thinking Munch (with Duke’s change) is on the right track. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 7:25pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/13 "2010-02-17T19:25:59Z")
> [@Munch](#): > > 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. 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). ---
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 7:30pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/14 "2010-02-17T19:30:51Z")
**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 ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 7:32pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/15 "2010-02-17T19:32:56Z")
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. ---
**Author:** ![Budista](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@Budista](https://boards.straightdope.com/u/Budista)\ **Post date:** [February 17, 2010, 8:01pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/16 "2010-02-17T20:01:57Z")
**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 ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 8:07pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/17 "2010-02-17T20:07:45Z")
Sure thing - not a problem. ---
**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\ **Post date:** [February 17, 2010, 8:21pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/18 "2010-02-17T20:21:00Z")
> [@Munch](#): > > 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. Ah–got that now. You are correct, I think. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 17, 2010, 8:29pm UTC](https://boards.straightdope.com/t/logic-expressions-in-ms-excel/529369/19 "2010-02-17T20:29:10Z")
> [@Duke](#): > > Ah–got that now. You are correct, I think. 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.