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

#### gmrush

##### New Member
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

Last edited:

#### gmrush

##### New Member
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!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

#### DanteAmor

##### Well-known Member
Works like a charm! Thank you, Dante! You ARE an Excel God!

1,141,314
Messages
5,705,689
Members
421,406
Latest member
kluna90

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back