I’m working on a project and I’m having some trouble figuring out a formula. I’ll try to keep this short and sweet.

I want to type:

“Kansas City, MO (83/46)” into A1

“.75” into B1

“2” into C1

“5” into D1

I then want to put a formula in E1 that will multiply the “46” from A1 by B1 “.75” and then multiply that number by C1 “2” and then add D1 “5” times the “46” from A1 again. In my head it looks like this:

=46*B1*C1+(D1*46) This will put 299 in E1

I just don’t know how to extract the 46 from A1.

For those that are curious…Kansas City, MO (83/46) represents a location that one of our employees is traveling to; 83 is the lodging rate and 46 is the per diem rate. When someone travels they receive .75% per diem for their first and last travel days (represented in B1 by the number 2), and 100% per diem for every full day they are on travel (represented in C1 by the number 5). So this traveler will receive $299 in per diem for traveling to Kansas City, MO leaving on a Sunday and returning the following Saturday.

=$46*.75*2+(5*46)