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

gmrush

New Member
Joined
May 27, 2015
Messages
13
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:

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
Joined
Apr 18, 2011
Messages
37,347
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try this


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

:confused: 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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top