IF/Then Statement in a collection with an AND statement

asked on May 11, 2020 • Show version history

Afternoon all! This calculation has been hurting my head. I have three fields in a collection for a mileage reimbursement form. For Context if you take your own vehicle and it's under 20 miles round trip, you get the full IRS mileage rate reimbursement. However, if it's over 20 miles, a company car was available and you still chose your own vehicle, you only get 25% of the rate for reimbursement. If it was over 20 miles and a company car wasn't available, you would get the full rate still.

=IF(AND(INDEX(Reimbursement_Information.Mileage,ROW())>20,Reimbursement_Information.Office_Vehicle_Available,ROW())="YES",INDEX(Reimbursement_Information.Mileage,ROW())*V_25__of_IRS_Reimbursement_Rate,INDEX(Reimbursement_Information.Mileage,ROW())*Current_IRS_Reimbursement_Rate)

0 0

SELECTED ANSWER

replied on May 12, 2020

It looks like you're missing an INDEX after the >20, as well as not closing off the AND. Try this:

=IF(
  AND(
    INDEX(Reimbursement_Information.Mileage,ROW())>20,
    INDEX(Reimbursement_Information.Office_Vehicle_Available,ROW())="YES"
  ),
  INDEX(Reimbursement_Information.Mileage,ROW())*V_25__of_IRS_Reimbursement_Rate,
  INDEX(Reimbursement_Information.Mileage,ROW())*Current_IRS_Reimbursement_Rate
)

2 0

replied on May 12, 2020

Hey Jim!

Thanks for the reply on this interesting calculation. I plugged in what you have and it's only giving me the mileage x the full IRS Reimbursement rate where I select Yes or No on the Office vehicle available.

Drew

0 0

replied on May 12, 2020

Ah, i think I found what it was. We were searching for "YES" when it should have been "Yes". Thanks so much for your help!

0 0

replied on May 12, 2020

That would certainly do it! Glad you got it resolved.

0 0

Question

Question

IF/Then Statement in a collection with an AND statement

Answer

Replies

Sign in to reply to this post.