desert_dweller5
New Member
- Joined
- Apr 8, 2011
- Messages
- 30
I'm trying to figure out how to figure out my mileage expenses automatically. It's kicking my butt.
lets start out simply.
every week I have to calculate my mileage expenses. following these rates and limits.
i get 54 cents per mile up to 225 miles in 1 week. after that I get 23 cents per mile. the rates start over the next week. I go back to 54 c / mile on sunday.
the problem comes in when I get to 226 miles for the week
this is what the result should look like. values selected for easy math.
also I may have to calculate a limit for 1000 mi / month. if I go over 1000 miles in a month I stop getting paid for that mileage.
I'm not sure where to calculate this on the spread sheet.
I'm not sure what formulas I need to make this happen. I've been at this for weeks and I still cant get it.
thanks for your time.
Desert_dweller5
Rate 1 | 0.54 |
Limit 1 | 225 |
rate 2 | 0.23 |
lets start out simply.
every week I have to calculate my mileage expenses. following these rates and limits.
i get 54 cents per mile up to 225 miles in 1 week. after that I get 23 cents per mile. the rates start over the next week. I go back to 54 c / mile on sunday.
the problem comes in when I get to 226 miles for the week
Day of the week | Daily | Weekly | 54 c/mi | 23 c/mile |
Sunday | 224 | 224 | =a2*0.54 (+$120.96) | 0 |
Monday | 2 | 226 | $0.54 | $0.23 |
Tuesday | 1 | 227 | 0 | $0.23 |
Wednesday | 1 | 228 | 0 | $0.23 |
Thursday | 1 | 229 | 0 | $0.23 |
Friday | 1 | 230 | 0 | $0.23 |
Saturday | 1 | 231 | 0 | $0.23 |
Current total | 231 | 121.50 | $1.15 | |
Grand total | Miles this month: | 231 | $122.65 |
this is what the result should look like. values selected for easy math.
also I may have to calculate a limit for 1000 mi / month. if I go over 1000 miles in a month I stop getting paid for that mileage.
I'm not sure where to calculate this on the spread sheet.
I'm not sure what formulas I need to make this happen. I've been at this for weeks and I still cant get it.
thanks for your time.
Desert_dweller5