CHOOSE function NOT RIGHT????

[Tomkat743]

The following formula was given to me but I don't think that I was explaining
my self very well. I have mapped a table below that I think explains it a
little better. I just feel like either Choose is not the right function or it
needs some help(much like me)ha ha. Any help appreciated, Thanks

=IF(OR('9508'!$E$2="",'9508'!$E$2=0),"",CHOOSE(TRUNC('9508'!$E$2/200)+1,100,200,300,400,500))

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 allowed and if <=max miles
allowed"" if >max miles allowed then = max miles

 

[Tom Ogilvy]

It is impossible to tell what you are showing and what you want.

If A1 holds 350, then

=Min(a1,formula to return max miles allowed)

would give you what you want. The original question never talked about a
range greater than 800. the formula I gave could be modified to accomodate
that)

 

[Dick Kusleika]

The following formula was given to me but I don't think that I was
explaining my self very well. I have mapped a table below that I
think explains it a little better. I just feel like either Choose is
not the right function or it needs some help(much like me)ha ha. Any
help appreciated, Thanks

=IF(OR('9508'!$E$2="",'9508'!$E$2=0),"",CHOOSE(TRUNC('9508'!$E$2/200)+1,100,200,300,400,500))

It is generally not desireable to have three or four threads for the same
question. I'm working on a thread entitled "Not quite there", while Tom and
Mat are addressing the same question in different threads. I don't know if
they have satisfactorily answered your question, but if they had and I had
spent any amount of time on it, it would seem like a big waste of my time.
Hopefully you can see my point of view.

When you don't get a response, it's usually due to just a few reasons. 1.
Your question is too broad which indicates you haven't done any prepatory
work on the problem. 2. Your explanations aren't clear. 3. Your subject
isn't descriptive.

Take a look here
http://www.dailydoseofexcel.com/archives/2006/04/21/two-variable-lookups/.
I tried to interpret you explanations in "Not quite there". If I missed
something, please respond to this post. When I reply to a post, my reply
turns red and all replies to it turn red. I never miss replies to my posts.
If you start a new thread, there's about a 5% chance I'll see it.

 

[Tomkat743]

I apologize for my misunderstanding of how this all works. I posted the
original question along with a couple of others and had no response over a 24
hour period. I determined that I was unclear with the information I was
providing so I reposted. I received a response that I tried to make work for
me but couldn't so I reposted again. I tried to map a table for my
explanation but I'm not sure it comes thru as a table. I also think Tom
Ogilvy may be giving me the corrected information but I still can't
understand it enough to implement it or it doesn't work for my senario. Thank
you for the explanation on how this site works I'm new to all this. Do you
think you could explain the formula that Tom has given me if you think it
will work? Thanks very much.

 

[Dick Kusleika]

I apologize for my misunderstanding of how this all works. I posted
the original question along with a couple of others and had no
response over a 24 hour period. I determined that I was unclear with
the information I was providing so I reposted. I received a response
that I tried to make work for me but couldn't so I reposted again. I
tried to map a table for my explanation but I'm not sure it comes
thru as a table. I also think Tom Ogilvy may be giving me the
corrected information but I still can't understand it enough to
implement it or it doesn't work for my senario. Thank you for the
explanation on how this site works I'm new to all this.

You're welcome. I learned it because someone explained it to me, so it's
the least I can do.

Do you think
you could explain the formula that Tom has given me if you think it
will work? Thanks very much.

I can explain that formula, but I don't think it's right for your
application. If you have one site, and therefore one scale of threshholds,
then the formula is fine. But you don't, so don't spend any more time on it
other than for your own edification.

My understanding is that you have multiple sites and each site has it's own
production level threshholds. You want to supply Actual Mileage, Production
Level, and Site and return Reimbursed Mileage. Look at the link I provided
and that I repeat below. If the method used in that link doesn't work, then
tell me specifically why. Tell what inputs I'm missing. Tell me what
inputs you've used that produce the wrong outputs.

 

[Tomkat743]

I'm sorry, I realize my initial description was unclear. I got the >800 when
I put it all in a table and realizied the range that I had given you
initially was $800-$999 = 500 mile cap when the truth is that anything over
$800 gets our max cap of 500 miles. I don't understand what you are talking
about below my sheet that holds the mileage is equal to that technicians
number and the cell is $E$2 or '9501'!$E$2 he can turn in as much or as
little mileage for reimbursement as he wants, we don't care because we cap it
based on his production dollar amount which resides on his same sheet in cell
$G$47 so for Technician 9501 we would look at cell '9501'!$G$47 to see how
much production he had for the week then we would look at cell '9501'!$E$2 to
see how many miles he turned in and if he turned in more than we allow we
would cap it based on the ranges I gave you. If he turned in less than we
allow we would be happy and just pay him for that amount. I ran your CHOOSE
function but I don't think it takes into account how much he made for the
week because it never looks at cell $G$47. Also it adjusts all mileage paying
out more to those who did not turn in the max. Hope this is a little more
clear. Thanks so much for all of your time.