Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

3 views

Skip to first unread message

Apr 20, 2006, 12:04:01â€¯PM4/20/06

to

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

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.

Apr 20, 2006, 3:17:01â€¯PM4/20/06

to

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

Apr 20, 2006, 3:32:01â€¯PM4/20/06

to

Okay, I found the original post, and I had a look at the example, so I came

up with the following:

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

Apr 20, 2006, 3:52:02â€¯PM4/20/06

to

Actually, you can probably skip the call to TRUNC() because it's dealt with

later by the CHOOSE() function anyway.

later by the CHOOSE() function anyway.

Apr 21, 2006, 12:30:23â€¯AM4/21/06

to

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.

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

>max miles force = max miles

Apr 21, 2006, 7:43:02â€¯AM4/21/06

to

"Tomkat743" wrote:

> 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

> >max miles force = max miles

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,

0 new messages

Search

Clear search

Close search

Google apps

Main menu