Thanks in advance.
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
=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
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
> 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,