I am trying to understand why this function is producing weird
results. I feel like I have setup the function correctly, but the
numbers being returned are not the numbers expected. Thanks for the
help,


Function FINDTier(ERNVAR, Tier As Range)

Select Case ERNVAR

Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.13: FINDTier = Tier(2)
Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)
Case Is <= 0: FINDTier = Tier(5)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is < -0.12: FINDTier = Tier(8)
End Select

End Function

I should also mention that I am working with percentages.

Hi Jim,

I think it's the sequence.

Suppose ERNVAR = -0.2, you expect the result Tier(8) but get Tier(5)? This
is because the case condition <=0 was True before the code could go to
< -0.12

Try something like:
Case Is = 0.13: FINDTier = Tier(2)
.. Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)
Case Is < -0.12: FINDTier = Tier(8)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= 0: FINDTier = Tier(5)




Wkr,

JP


"Jim" wrote in message
...
I am trying to understand why this function is producing weird
results. I feel like I have setup the function correctly, but the
numbers being returned are not the numbers expected. Thanks for the
help,


Function FINDTier(ERNVAR, Tier As Range)

Select Case ERNVAR

Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.13: FINDTier = Tier(2)
Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)
Case Is <= 0: FINDTier = Tier(5)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is < -0.12: FINDTier = Tier(8)
End Select

End Function

If you first case (0.13) is met then so will the next 3 cases too - it won't
ignore the commands because the first case was satisfied.

you want:

Case Is = 0.09, Is <0.13: FINDTier = Tier(3)

etc

"Jim" wrote:

I am trying to understand why this function is producing weird
results. I feel like I have setup the function correctly, but the
numbers being returned are not the numbers expected. Thanks for the
help,


Function FINDTier(ERNVAR, Tier As Range)

Select Case ERNVAR

Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.13: FINDTier = Tier(2)
Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)
Case Is <= 0: FINDTier = Tier(5)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is < -0.12: FINDTier = Tier(8)
End Select

End Function

Sam Wilson;459271 Wrote:
If you first case (0.13) is met then so will the next 3 cases too -
_it_won't
ignore_the_commands_because_the_first_case_was_sat isfied_.

you want:

Case Is = 0.09, Is <0.13: FINDTier = Tier(3)

etc
In Excel 2003 at least, the underlined statement above is *not true*!
As soon as one condition is satisfied the rest are ignored, whether or
not they might be true.
From the help file:
"If testexpression matches an expressionlist expression in more than
one Case clause, only the statements following the *first *match are
executed."


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127089

Make sure you ask in nice order (like P45Cal said):

Select Case ERNVAR
Case Is < -0.12: FINDTier = Tier(8)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= 0: FINDTier = Tier(5)

'what happens between 0 and .05???

'.13 is duplicated in your code
'which tier did you want?
Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.13: FINDTier = Tier(2)

Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)

End Select

But that mixture of < and makes the code difficult for me.

This makes my head spin much less:

Select Case ERNVAR
Case Is < -0.12: FINDTier = Tier(8)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= 0: FINDTier = Tier(5)

Case Is < 0.05: FINDTier = "Not defined"

Case Is < 0.09: FINDTier = Tier(4)
Case Is < 0.13: FINDTier = Tier(3)
Case Else
FINDTier = Tier(1)
End Select

Kind of like a postage stamp function. As soon as your package hits a certain
weight, it's categorized one way. And all packages that don't exceed the next
limit are the same price as your package.





Jim wrote:

I am trying to understand why this function is producing weird
results. I feel like I have setup the function correctly, but the
numbers being returned are not the numbers expected. Thanks for the
help,

Function FINDTier(ERNVAR, Tier As Range)

Select Case ERNVAR

Case Is 0.13: FINDTier = Tier(1)
Case Is = 0.13: FINDTier = Tier(2)
Case Is = 0.09: FINDTier = Tier(3)
Case Is = 0.05: FINDTier = Tier(4)
Case Is <= 0: FINDTier = Tier(5)
Case Is <= -0.03: FINDTier = Tier(6)
Case Is <= -0.07: FINDTier = Tier(7)
Case Is < -0.12: FINDTier = Tier(8)
End Select

End Function

--

Dave Peterson