Select Case or some such If type statement

[WillRiley]

Hi people,

I have the following poser that I could do with some help on.

I am trying to match the average balance of an account with
a set of "levels" that determine what rate of interest is to be
applied to the said account. Trouble I am having is that of the
7 levels, not all of them are always used. I have been trying to
use select case to avoid a very lengthy IF statement but I
wondered if there was some form of boolean array option that
would suit my purpose better - I solved the issue in Excel using
a combination of MATCH & OFFSET, but i need a crystal solution
as it's much quicker calculating the results...

Here's the current formula

Select {dstt.avg_bal}
Case 0 to {dmd.lev2}-0.01:
{?Base}+Cdbl({dmd.rate1})
Case {dmd.lev2} to {dmd.lev3}-0.01:
{?Base}+Cdbl({dmd.rate2})
Case {dmd.lev3} to {dmd.lev4}-0.01:
{?Base}+Cdbl({dmd.rate3})
Case {dmd.lev4} to {dmd.lev5}-0.01:
{?Base}+Cdbl({dmd.rate4})
Case {dmd.lev5} to {dmd.lev6}-0.01:
{?Base}+Cdbl({dmd.rate5})
Case {dmd.lev6} to {dmd.lev7}-0.01:
{?Base}+Cdbl({dmd.rate6})
Case UpFrom {dmd.lev7}:
{?Base}+Cdbl({dmd.rate7})

I need to amend it so that if there are, say only 3 levels and the balance
is say > lev3, it returns rate3 - currently it seems to give me rate7 due to
the fact that UpFrom lev7 equates to UpFrom 0 - rates will only be filled in
if the levels have values... so if you have a value for lev2 & lev3 only, you will have
values for rate1, rate2 & rate3

Hope you can see what I am trying to achieve - sorry for the lengthy post
I fyou need further details, just shout,
Regards,

Will

 

[WillRiley]

OK, I have a working solution using what I would have liked to avoid... a very lengthy IF statement.

If anyone can see a way to make this neater/faster, please jump in,

Thanks to anyone who looked at this in the meantime

//special step rate calculator

If {dstt.avg_bal} >=0 And {dstt.avg_bal} < {dmd.lev2}
Then cdbl({dmd.rate1})

Else If {dstt.avg_bal} >={dmd.lev2} And {dstt.avg_bal} < {dmd.lev3}
Then Cdbl({dmd.rate2})

Else If {dstt.avg_bal} >={dmd.lev3} And {dstt.avg_bal} < {dmd.lev4}
Then Cdbl({dmd.rate3})

Else If {dstt.avg_bal} >={dmd.lev4} And {dstt.avg_bal} < {dmd.lev5}
Then Cdbl({dmd.rate4})

Else If {dstt.avg_bal} >={dmd.lev5} And {dstt.avg_bal} < {dmd.lev6}
Then Cdbl({dmd.rate5})

Else If {dstt.avg_bal} >={dmd.lev6} And {dstt.avg_bal} < {dmd.lev7}
Then Cdbl({dmd.rate6})

Else If {dstt.avg_bal} >={dmd.lev7} And {dmd.lev7} <> 0
Then Cdbl({dmd.rate7})

Else If {dstt.avg_bal} <0 Then 0

Will