×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula Help
2

Formula Help

RE: Formula Help

Arvarr,

Before I get up from my recliner, pour myself another cup of coffee, take off my lounging garb and put on my boots, I'd like you to check your data (both sheets).

For one I see a STATUS that's not in the Table sheet. That's not gonna work! So when I saw that at a mere glance, I stopped looking. That's YOUR job.

Use the tools at your disposal. Check your data. Make sure that you have a rule in Table for EVERY data combination in your Data sheet. I mean, I found this one in less than a minute.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

...and let me give you a clue. BIG RED FLAG!
For Len=1 you have LOTS and LOTS of FALSE. ???
WHY???
Did you only add 5 new outcomes for Len=2???

Something else has changed that gonna take a LOT of analysis, and my cup of coffee'll get cold!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
HI Skip
It appears the outcome from the code was different to the table. I have since updated the code.
There are still 4 scenarios that is providing false.

Table - Row 21 & 22 - False as it wasn't in the original code provided
Outcome 26 & 27 - Not sure why the UDF not providing any outcomes

Len 2 - I have only added 4 new outcomes for Len = 2.
This is because i have data recently introduced from another source and the Len = 2 serves as an identifier.

Thanks,
arv

RE: Formula Help

As the set of rules evolves, I would try the opposite: check outcomes in separate procedures, starting from:

CODE -->

Function ChkOutcome01(sTyp As String, sSta As String, iLen As Integer) as Boolean
If sTyp = "Invoice" and sSta = "C" and iLen = 1 then
ChkOutcome01 = True
Else
ChkOutcome01 = False
End If End Function

The Outcome function using single criterion evaluation:

CODE -->

Function Outcome(sTyp As String, sDep As String, iCnt As Integer, nMon As Single, ' etc, all arguments you need
Select Case True
Case ChkOutcome01(sTyp, aSta, iLen)
Outcome = "Outcome 1"
' etc. Case Else
Outcome = "No match"
End Select End Function


combo

RE: Formula Help

(OP)
Hi Combo
Not sure that i followed you.

Thanks,
arv

RE: Formula Help

I mean: build 35 support functions to properly assign to given outcome. Next In final UDF use them to have proper output. The code below should be extended to cover all criteria. The final function can be used as UDF: =Outcome2(A2;B2;C2;D2;E2;F2;G2), one more argument than existing Outcome UDF.

The conditions either cannot have common ranges (better) or have to be evaluated in proper criteria order, or you have to implement nested logic. Select Case will quit evaluation after first True.
I put ChkOutcome30 as first, as it seems to have priority. However, this interfere with condition 35. Maybe it would be better to use one more argument: Function ChkOutcome30(sSta as String, nAMT As Currency) and have conditions If nAMT >= -100 And nAMT <= 100 And sSta <> "Pending" Then, to have room for "Outcome 35".

The starting point, without implementing the last remark:

CODE -->

Function Outcome2(sTyp As String, sDep As String, iCnt As Integer, nMon As Single, sSta As String, iLen As Integer, nAMT As Currency) As String
Select Case True
Case ChkOutcome30(nAMT)
    Outcome2 = "Outcome 30"
Case ChkOutcome01(sTyp, sSta, iLen)
    Outcome2 = "Outcome 1"
Case ChkOutcome02(sTyp, sSta, iLen)
    Outcome2 = "Outcome 2"
Case ChkOutcome03(sTyp, sDep, iLen)
    Outcome2 = "Outcome 3"
' etc.
' finally none of conditions satisfied
Case Else
    Outcome2 = "No match"
End Select
End Function

' support functions for criteria 1-3 and 30 (assumed that it is the priority)
' process other criteria in a similar way
Function ChkOutcome01(sTyp As String, sSta As String, iLen As Integer) As Boolean
If sTyp = "Invoice" And sSta = "C" And iLen = 1 Then
ChkOutcome01 = True
Else
ChkOutcome01 = False
End If
End Function

Function ChkOutcome02(sTyp As String, sSta As String, iLen As Integer) As Boolean
If sTyp = "Invoice" And sSta = "E" And iLen = 1 Then
ChkOutcome02 = True
Else
ChkOutcome02 = False
End If
End Function

Function ChkOutcome03(sTyp As String, sDep As String, iLen As Integer) As Boolean
If sTyp = "Invoice" And sDep = "A001" And iLen = 1 Then
ChkOutcome03 = True
Else
ChkOutcome03 = False
End If
End Function

Function ChkOutcome30(nAMT As Currency) As Boolean
If nAMT >= -100 And nAMT <= 100 Then
ChkOutcome30 = True
Else
ChkOutcome30 = False
End If
End Function 

combo

RE: Formula Help

(OP)
Hi combo
Thanks for elaborating the codes.

I have managed to expand the 35 support functions > have the proper output and also included the extra function argument to allow room for output 35.

Thank you so much for your help. Much appreciated. It has saved me lots of time.

Thanks,
arv

RE: Formula Help

(OP)
HI Skip
Thanks so much for looking into this and flagging where i have the incorrect data and table.

As always, you're a great help :)

Till next time.

Thanks,
arv

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close