Question

Using If statements to calculate an amount on an expense report.

Forms Version 11

Updated October 7, 2021

asked on October 6, 2021

We are using a table to collect travel information on an expense report. When the user selects "Mileage Reimbursement" from the drop down, and enters the miles travelled, we want it to use the milage rate on the form to automatically calculate the amount. Here are the three fields involved:

And here is the formula so far:

What do I need to add or change here to get it to calculate when they select that option from the dropdown. If they select anything other than "Mileage Reimbursement", we don't want it to calculate anything so they can enter the correct amount for whatever the expense is.

0 0

Replies

replied on October 6, 2021

I had this issue as well. What I did was assign a numerical value to the drop down field options. For example, to Gas you assign the value 1. You also need to index the row and add the last parameter (What the value is when the condition is false). So it would be:

=IF(INDEX(Transportation.Type,ROW())=1,MULT(INDEX(Transportation.Miles_Driven,ROW()),INDEX(Accounting_Details.Mileage_Rate,ROW())),0)

I followed that format for mine and worked. It assigns the value 0 when the condition is false but you can change that.

0 0

replied on October 7, 2021

That worked perfectly! Unfortunately, the end users switched to a look up table for the dropdown to fill in a billing code, now I need to come at it a different way. When this number is autofilled to billing code, do the math. Thoughts?

0 0

You are not allowed to follow up in this post.

Question

Question

Using If statements to calculate an amount on an expense report.

Replies

Sign in to reply to this post.