Formula Help 2

[arvarr]

thread68-1802367

Hi Skip
Sorry, had to revisit this thread.

Table worksheet > Column G is the additional requirement. Row 32-36 also new requirements.
Data worksheet > Column F is the additional data

Is there an easy way to incorporate the change into your code?

Thanks,
arv

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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

 

[combo]

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

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

The Outcome function using single criterion evaluation:

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)
[indent]Outcome = "Outcome 1"[/indent]
' etc.
Case Else
[indent]Outcome = "No match"[/indent]
End Select
End Function

combo

 

[arvarr]

Last file i upload seemed to have removed the code.
Reuploading.

 

[arvarr]

Hi Combo
Not sure that i followed you.

Thanks,
arv

 

[combo]

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: [tt]Function ChkOutcome30(sSta as String, nAMT As Currency)[/tt] and have conditions [tt]If nAMT >= -100 And nAMT <= 100 And sSta <> "Pending" Then[/tt], to have room for "Outcome 35".

The starting point, without implementing the last remark:

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

 

[arvarr]

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

 

[arvarr]

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