The following is some help I got trying to figure out a formula to put a cap
on mileage turned in for reimbursement. Our payroll takes the mileage turned
in and looks at the $ amount of production that was turned in by that
individual. If the mileage turned in is greater than what was allowed for
that particular dollar amount of production we lessen the mileage or "cap it"
based on the table below. If the mileage turned in is less than or equal to
what was allowed we do not adjust it. The mileage is pulled off a daily
sheet and talleyed on line $E$2 of the weekly production sheet for each
individual. Every individual has a weekly production sheet in a workbook
where line E2 is mileage and line G47 is production. The process is similar
in all of our locations although the mileage cap varies from site to site.
The following represents one paticular location whereas we may allow 150
miles for the same amount of production in another system. in other systems
we may allow the same amount of miles but base it on less or more production.
My initial thought was to use an Index and Match formula but I couldn't make
it work. When I tried this one it worked except for the ones where the
mileage was less than the cap. This one gives them miles that they didn't
turn in. Unless I'm not understanding and putting the wrong values in it. The
formula written by Mr. Ogilvy was:
=if(or(e3="",e3=0),"",CHOOSE(TRUNC(E3/200)+1,100,200,300,400,500)) The
formula as I tried it
was:=IF(OR('9508'!$E$2="",'9508'!$E$2=0),"",CHOOSE (TRUNC('9508'!$E$2/200)+1,100,200,300,400,500))

The rest is our original thread. Hope someone can help, Thanks.

--
Regards,
Tom Ogilvy


"Tomkat743" wrote:

Please help I think this is fairly easy but i'm kinda in a hurry, Thanks.
"I'm not sure Vlookup is the function I want.

The cap is as follows.

$100. - $199. = up to 100 miles allowed, (less is ok we do not move them up.)
$200 - $399. = up to 200 miles allowed.
$400 - $599 = up to 300 miles allowed
$600 - $799 = up to 400 miles
$800 - $999 = 500 miles
I will pull the payroll amount from sheets 9501, 9502, 9503, etc. cell "G48"
On the Employee sheet there is a cell "E30" which has the formula ='9501'!E2
which is where all of 9501's mileage is pulled from for the week. "E31" is
the cell for "9502" Again if the mileage is within range we just leave it
alone