IF, OR, CHOOSE, TRUNC Help with formula please

[Tomkat743]

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.

 

[Mat P:son]

Hi there,

I got a little confused by this description, so I hope you may be able to
help me out a little.

1) If cell G47 is supposed to control the capping, then where does a
reference to cell G47 show up in the formula below?

2) You are refering to a table, but there isn't one -- since this post seems
to be a follow-up from an earlier post, did the table perhaps get truncated
together with all of the previous discussions?

3) You are also refering to a specific figure of 150, which I cannot see
anywhere in the formula below.

Anyhow, if you could perhaps provide a few suitable examples I'm sure this
issue could be sussed...

And another thing -- try breaking the formula up into its varius components.
Then you'll probably gain a much better understanding of what's happening
(that's what I normally do when something's not quite working, because I find
the Excel formula very tricky to read

Cheers,
/MP

 

[Mat P:son]

Okay, I found the original post, and I had a look at the example, so I came
up with the following:

=IF(OR('9508'!$E$2="";'9508'!$E$2<100);"";IF('9508'!$E$2>=1000;500;CHOOSE(TRUNC('9508'!$E$2/200)+1;100;200;300;400;500)))

This formula will:
a) Check for empty cell values (""), and return empty string ("")
b) Similarly, values below 100 cause the formula to return empty string as
well
c) Values of 1000 or above will be maxed out at 500
d) All other in between will be grouped into $200 brackets

Will this do the trick, or is there something I still haven't figured out
quite yet?

The solution isn't very neat though, but as long as it works... I s'pose you
can always spend time polishing it later, when you feel you've got nothing
better to do

Cheers,
/MP

 

[Mat P:son]

Actually, you can probably skip the call to TRUNC() because it's dealt with
later by the CHOOSE() function anyway.

/MP

 

[Tomkat743]

Thank you for the post sorry so long to respond. I just think we are taking
the wrong path here with the CHOOSE FUNCTION. All the senarios work to cap
everyones miles but the problem remains that I don't want to increase miles
turned in that are below the cap in the first place. You are right to
question the reference to G47 we must use that as a reference point for each
cap. a table would be something like this.

mileage turned in $amount of production break points max mileage
allowed
9501$E$2 $G$47 <$100
none
9501$E$2 $G$47 $100-$199
100 miles
9501$E$2 $G$47 $200-$399
200 miles
9501$E$2 $G$47 $400-$599
300 miles
9501$E$2 $G$47 $600-$799
400 miles
9501$E$2 $G$47 >$800
500 miles

The senario we have not accounted for is if someone has (mileage turned in
=350 miles) ($amount of production = $800) (Break point used = >$800) (max
mileage allowed = 500) We want to pay only for the 350 miles turned in. So
the formula would have to look at this table and say look at $G$47 and find
which range it belongs to, then look at max miles and if <=max miles "" if

 

[Mat P:son]

Thank you for the post sorry so long to respond.

No probs

I just think we are taking
the wrong path here with the CHOOSE FUNCTION.

To be honest, I'm not sure it's the wrong path. If the brackets were a bit
more uniform you could easily have used maths to calculate the cap, but with
the current situation I think CHOOSE() will do the trick. It's not very
pretty, but, in my humble opinion, no formulae in Excel ever are...

All the senarios work to cap
everyones miles but the problem remains that I don't want to increase miles
turned in that are below the cap in the first place.

Well, that's fair enough

You are right to
question the reference to G47 we must use that as a reference point for each
cap. a table would be something like this.

mileage turned in $amount of production break points max mileage
allowed
9501$E$2 $G$47 <$100
none
9501$E$2 $G$47 $100-$199
100 miles
9501$E$2 $G$47 $200-$399
200 miles
9501$E$2 $G$47 $400-$599
300 miles
9501$E$2 $G$47 $600-$799
400 miles
9501$E$2 $G$47 >$800
500 miles

Okay, that makes sense...

The senario we have not accounted for is if someone has (mileage turned in
=350 miles) ($amount of production = $800) (Break point used = >$800) (max
mileage allowed = 500) We want to pay only for the 350 miles turned in. So
the formula would have to look at this table and say look at $G$47 and find
which range it belongs to, then look at max miles and if <=max miles "" if

That's all right then. Now, let me see if I get this straight (I use some
pseudo-code just to clarify things a bit). Is this exactly what you're after:

==============================

//
// Some values we use
//
produced : G47
submitted : E2
scaling : 200
allowed : <temporary>
paidout : <result>

//
// Figure out which bracket we end up in, and, hence, which cap to use
// (notice the optimised CHOOSE, which now only handles cap values up to 400)
//
IF (produced =="" || produced < 100)
allowed = 0
ELSE IF (produced >= 800)
allowed = 500
ELSE
allowed = CHOOSE(produced / scaling + 1 : 100, 200, 300, 400)
END IF

//
// Determine what to pay for
// (we should also check that "submitted" is valid, but we don't at the mo...)
//
paidout = MIN(submitted, allowed)

==============================

If this is pretty much what you're after then we're done:

-------------------------------------------

=MIN('9508'!$E$2;IF(OR('9508'!$G$47="";'9508'!$G$47<100);"";IF('9508'!$G$47>=800;500;CHOOSE('9508'!$G$47/200+1;100;200;300;400))))

Or, slightly more readable, perhaps:

=MIN(
submitted;
IF(OR(produced="";produced<100);
"";
IF(produced>=800;
500;
CHOOSE(produced/scaling+1;100;200;300;400))))

-------------------------------------------

Notice that G47 has now replaced E2, and that E2 is taken into consideration
later in the computation chain, when we try to figure out what should
actually be paid out.


Cheers,
/MP

Cheers,