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))

Thanks in advance.