Good morning,



I have a travel expense form that I created (very simplistic) I have set the
Total Private Vehicle Miles traveled to "0" for then number so when you enter a number such as 10.5 it shows 11. Then when the person types into that field 10.5, the next column (rate per mile) has this calculation =IF(M30>=1,0.56,"") then the third column has this calculation (Reimbursed PMV) =IF(M31>=1,((M31-O31)*P31),"") does the math and we get the total.

However, since I'm not that experienced in excel I found that it actually wasn't calculating correctly like I thought. It was using the data entered 10.5 instead of 11 and not rounding up.

I'm sure it's something simple, but when I input round and try that it gives me an error message. I have several rows for people to input multiple travel miles but didn't but it all in.

Any Help would be appreciated. I can also send the spreadsheet I have created for someone to look at. Thank you.

Rosie

It will be much easier if you attach your sheet. Your references are not clear to me.

I don't see where to attache the file. I have copied the cells below if that helps.

table { }.font0 { color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; }.font5 { color: windowtext; font-size: 10pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl67 { white-space: normal; }.xl68 { font-size: 12pt; font-family: Arial,sans-serif; white-space: normal; }.xl69 { font-size: 8pt; text-align: left; white-space: normal; }.xl70 { font-size: 8pt; text-align: left; border-width: 0.5pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; white-space: normal; }.xl71 { font-size: 8pt; text-align: left; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; white-space: normal; }.xl72 { font-size: 8pt; text-align: left; border-width: 0.5pt medium medium 0.5pt; border-style: solid none none solid; border-color: windowtext -moz-use-text-color -moz-use-text-color windowtext; white-space: normal; }.xl73 { font-size: 8pt; text-align: center; white-space: normal; }.xl74 { font-size: 8pt; font-style: italic; text-align: center; border-width: 0.5pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl75 { font-size: 8pt; font-style: italic; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl76 { font-size: 8pt; text-align: center; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; white-space: normal; }.xl77 { font-size: 8pt; text-align: center; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; white-space: normal; }.xl78 { font-size: 8pt; text-align: center; border-width: medium medium 0.5pt 0.5pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color windowtext windowtext; white-space: normal; }.xl79 { text-align: center; border-width: 0.5pt 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl80 { text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl81 { text-align: center; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; white-space: nowrap; }.xl82 { text-align: center; border: 0.5pt solid windowtext; white-space: nowrap; }.xl83 { text-align: center; border-width: 0.5pt 0.5pt 1pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl84 { text-align: center; border-width: 0.5pt 0.5pt 1pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl85 { white-space: normal; }.xl86 { border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: nowrap; }.xl87 { font-weight: 700; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: normal; }.xl88 { text-align: center; border-width: 0.5pt medium 0.5pt 1pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; white-space: nowrap; }.xl89 { font-size: 8pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; border-width: 1pt medium 0.5pt 1pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl90 { font-size: 8pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; border-width: 1pt medium 0.5pt; border-style: solid none; border-color: windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl91 { font-size: 8pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; border-width: 1pt 1pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl92 { font-size: 8pt; text-align: left; border-width: 0.5pt 0.5pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; white-space: normal; }.xl93 { font-size: 8pt; font-style: italic; text-align: center; border-width: 0.5pt 1pt 0.5pt 0.5pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }.xl94 { font-size: 8pt; text-align: center; border-width: medium medium medium 0.5pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; white-space: normal; }.xl95 { text-align: center; border-width: 0.5pt 1pt 0.5pt 0.5pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl96 { text-align: left; border: 0.5pt solid windowtext; white-space: nowrap; }.xl97 { text-align: center; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; white-space: nowrap; }.xl98 { text-align: center; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; white-space: nowrap; }.xl99 { font-weight: 700; text-align: center; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: nowrap; }.xl100 { font-weight: 700; text-align: center; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: nowrap; }.xl101 { text-align: right; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; white-space: nowrap; }.xl102 { text-align: right; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; white-space: nowrap; }.xl103 { text-align: center; border-width: 0.5pt 1pt 1pt 0.5pt; border-style: solid; border-color: windowtext; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: nowrap; }.xl104 { text-align: center; border: 0.5pt solid windowtext; white-space: nowrap; }.xl105 { font-weight: 700; text-align: right; vertical-align: middle; border-width: medium medium 0.5pt 0.5pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% white; white-space: normal; }.xl106 { font-weight: 700; text-align: right; vertical-align: middle; border-width: medium medium 0.5pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% white; white-space: normal; }.xl107 { font-weight: 700; text-align: right; vertical-align: middle; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% white; white-space: normal; }.xl108 { font-weight: 700; text-align: right; vertical-align: middle; border-width: 0.5pt medium; border-style: solid none; border-color: windowtext -moz-use-text-color; white-space: normal; }.xl109 { font-weight: 700; text-align: right; vertical-align: middle; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; white-space: normal; }.xl110 { font-weight: 700; vertical-align: middle; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: nowrap; }.xl111 { font-weight: 700; vertical-align: middle; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); white-space: nowrap; } For office use only 13. 14. 15. 16. 17. 18. 19. Date or
date range Cost code Cost code percent Date
mm/dd/yy Miscellaneous expenses:
Fares, private mileage, telephone, and other (include receipts). Total PVM travelled Commute miles Rate per mile Reimbursed PVM Non-mileage amount PMV rate 0.56 20. Section total

I found where to attach it and unlocked the form so you can see what it looks like

You always want to round up, eh? That's very generous of you. ;-)

At any rate, regardless how you have a cell formatted, the underlying value remains the same.

Here's your formula:
=IF(M28>=1,((ROUNDUP(M28,0)-ROUNDUP(O28,0))*P28),"")

Thanks so much. That worked perfectly. I really appreciate you sending the formula. I just couldn't figure it out. You are a wizard!

In fact, get rid of the if >=1. You don't need it.

=((ROUNDUP(M28,0)-ROUNDUP(O28,0))*P28)

I just got asked to change it again and I'm again not sure how to do that. They would like me to round down if it's less then .4 and round up if it's .5 or greater. Guess they aren't that generous Could you give me an example of how to put the calculation in? I appreciate it. Thank you so much in advance.

That's easy, but it's kind of stupid. Maybe they have a good reason to want to do that but it sounds ridiculous to me.

Just delete the UPs from the ROUNDUPs.

=((ROUND(M28,0)-ROUND(O28,0))*P28)

Good morning,

Thank you so much for replying to me. I put the info in that you gave me and it works great, however when I put in the calculation it show "#value" after I change the calculation. It goes away after I enter a number, but is there a way to make that hide? Thank you so much for showing me how to do this. I appreciate it.

Please post a sampls sheet. To do this click on " go advanced" then on " manage attachments" and follow the wizard

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

=iferror(((round(m28,0)-round(o28,0))*p28),"")

Here's the sample sheet attached with the "value" error in it. It works fine, but the value doesn't disappear until you put a number in for the milage and tab over. Thanks for the help!

Attached Files

CO-A-00016 Travel Expense Form 10_13_14.xlsx (82.2 KB, 8 views)

See my post just above. That should work.

There's nothing I can do with the file you just attached since it is password protected.

Good morning,

Sorry, I forgot to unprotect it. It's attached now unprotected.

Attached Files

CO-A-00016 Travel Expense Form 10_13_14 unlocked.xlsx (81.8 KB, 10 views)