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

 

[shg]

We'd need to understand how your data is arranged, and some examples with supporting rationale.

 

[gmrush]

We'd need to understand how your data is arranged, and some examples with supporting rationale.

The data is arranged in columns, as follows. Columns B, C, D, E, and G are entered by the employee. I am trying to enter the formula in column I, based upon what the employee types in Column C.

[/IMG]

 

[gmrush]

Here is what it would look like with some data in it.

[/IMG]

Note, however, that the amount in column I is incorrect, because the formula current subtracts 30 miles before multiplying the mileage times .50. In this instance, the character string "storage" exists in Column C, so the forumula in I should NOT subtract 30 miles before running the calculation.

 

[DanteAmor]

Do you need something like this?

=MAX((G14-IF(C14="storage",-30,0))*0.5,0)

 

[gmrush]

Do you need something like this?

=MAX((G14-IF(C14="storage",-30,0))*0.5,0)

I think that might work, but wouldn't the syntax be -IF(C14="storage",0,-30)? We only need to subtract 30 miles if C14 is NOT equal to storage.

 

[gmrush]

Ok....if I reverse the order of -30 and 0, it works correctly. -30 becomes 30, not -30 since the minus sign is already in existence before "IF". Is it possible to use a statement that says "IF(C14 contains "storage")? I am looking at the reimbursement forms and sometimes the employee enters storage unit.

 

[DanteAmor]

Ok....if I reverse the order of -30 and 0, it works correctly. -30 becomes 30, not -30 since the minus sign is already in existence before "IF". Is it possible to use a statement that says "IF(C14 contains "storage")? I am looking at the reimbursement forms and sometimes the employee enters storage unit.

You're right, it should be only a sign

=MAX((G14-IF(C14="storage",30,0))*0.5,0)

 

[gmrush]

You're right, it should be only a sign

=MAX((G14-IF(C14="storage",30,0))*0.5,0)

Thank you, Dante. Expanding on this, I now need to evaluate the contents of a user input cell and based upon what has been typed, change it to a specific text string. The text string has to match the text string in the formula or it is ignored.

Here is a clip of the data I am working with:

[/IMG]

In cell A14, I need a formula that looks at C14 and determines if the cell = "storage". If it contains "storage unit", I need to be able to replace "storage unit" with "storage". The reason for this is a formula running in H14 that looks for the word "storage" in C14 and if it finds "storage", the formula does NOT subtract 30 miles (exempted miles). If "storage" is found in C14, the value in H14 is set to 0, which is correct.

This is the formula in H14:
=IF(C14="","",IF(AND(C14="",G14>=0),30,IF(AND(C14="storage",G14>=0),0,30)))

The problem arises when the employee types anything other than "storage" (i.e.,storage unit), the formula automatically enters 30 into H14 (which is incorrect).

I think the only way to handle this "search and replace" type of function is with VBA (which I'm not very good with). Any ideas?

 

[DanteAmor]

Try this


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