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