Formula Help Needed - "If Cell Contains Text String, then run this calculation =MAX(((G14-30)*0.5),0)"

[gmrush]

I am working with an expense reimbursement report that takes into consideration several scenarios for mileage:

1. The first 30 miles driven of a total one-way trip are not reimbursed if the employee leaves from his/her residence.
2. If an employee leaves on a trip from the company storage unit (electrical contractor), all of the mileage is reimbursed the first direction but not the second; the return trip subtracts 30 miles as the employee is traveling from the jobsite to their residence.
3. Mileage is reimbursed at the rate of .50 per mile.

I have the formula for calculating the mileage correct, but failed to notice that it does not take into consideration that it should not subtract the 30 miles if the employee has started the trip from the storage unit.

Therefore, I need a modification of the formula:

=MAX(((G14-30)*0.5),0)

that:
- searches for the word "storage" in cells C14 through C35 and if found, DOES NOT subtract 30 miles before multiplying the total mileage times .50.

I cannot wrap my head around how to skin this cat. Do I need a helper column? Can it be done with one formula?

Any help would be appreciated. I need to get this resolved quickly!

Thank you,

Gina

 

[gmrush]

Try this


=IF(C14="","",IF(AND(C14="",G14>=0),30,IF(AND(LEFT(C14,7)="storage",G14>=0),0,30)))

Works like a charm! Thank you, Dante! You ARE an Excel God!

 

[Rick Rothstein]

Try this


=IF(C14="","",IF(AND(C14="",G14>=0),30,IF(AND(LEFT(C14,7)="storage",G14>=0),0,30)))

If you have what I highlighted in red, how will what I highlighted in blue ever be evaluated? I am pretty sure the IF function call with what I highlighted in blue can be removed.

 

[DanteAmor]

Works like a charm! Thank you, Dante! You ARE an Excel God!

Im glad to help you, thanks for the feedback.