Help with formula for scenarios

[Lillian]

Hi,
Im trying to create a formula that will provide me with a answer according to the details I enter in the designated columns. Example: Medicaid eligibility, I'd need to enter there info like: age, income, assets etc...according to that info I need to determine what program they r eligible for, or what process to take in order to make them eligible. I there any way to use excel to determine what action to take for each patient separately?
waiting to hear back.
Thank You,
Lillian H.

[Paul_Hossler]

I think you'd need to come up with the complete set of business rules and criteria fields

Example:

Criteria fields:

Age
Income
Assets


Rules:

If Age < 59 then "Not Eligible"

If Income > 40,000 then "Not Eligible"

If Income > 30,000 and Assets > 50,000 then "Not Eligible"

If Income > 30,000 and Assets <= 50,000 then "Eligible"

[SamT]

All things considered. . . No.

The Welfare Biz is such a lucrative field, that if such a thing were reasonably possible, somebody would already be marketing it.

Some specific 'objections' I see:
Each program's requirements can run hundreds of pages. The requirements change with each Federal Admin change, each State admin change, each Program admin change, and with each lawsuit brought across the Nation. The actual requirements require fifteen lawyers, nine Judges, and six accountants, per program, to accurately describe.

Like Paul says, we can build it, but it's usability and accuracy are strictly dependent on you and your knowledge and your ability to clearly and correctly pass that knowledge to us. And with zero guarantees that the Requirements won't change before its completed.

[Lillian]

But if I am familiar with all the regulations and rules, what would I need to do in order to set it up?

[SamT]

Follow Paul's instructions for Each Program

[Paul_Hossler]

But if I am familiar with all the regulations and rules, what would I need to do in order to set it up?

See #2

BUT .... if it's too long and complex most likely people will only give hints and tips

[Lillian]

So can you at least tell me what function to focus on using? I want to know in which direction to do my research in.

[Paul_Hossler]

I think you'll need to create a very detailed decision tree first


(1) If Age < 59 then
"Not Eligible"
Stop

(2)ElseIf Income > 40,000 then
"Not Eligible"
Stop

(3)ElseIf Income > 30,000 and Assets > 50,000 then
"Not Eligible"
"Stop

(4)ElseIf Income > 30,000 and Assets <= 50,000 then
"Eligible



Etc.

The various limits would be stored in a 'database' like worksheet. I used named ranges ( the [...] stuff) and a user function to stay with the worksheet paradigm

Normally I'd have a userform to capture information

Option Explicit

Function Decision(Age As Double, Income As Double, Assets As Double, Current As String) As Variant

    If Age = 0 Then
        Decision = vbNullString
        Exit Function
    End If

    If Age < [MinAgeToApply] Then
        Decision = "Too young to apply. Must be at least " & [MinAgeToApply] & " years old"
        Exit Function
    End If

    If Age < [MinAge] Then
        If UCase(Left(Current, 1)) = "Y" Then
            Decision = "Eligible for Additional Supplimental Benefits"
        Else
            Decision = "Not eligible (age < " & [MinAge] & ")"
        End If
        Exit Function
    End If

    If Income > [MaxIncome] Then
        Decision = "Not eligible (income > " & Format([MaxIncome], "$#,##0") & ")"
        Exit Function
    End If

    If Assets > [MaxAssets] Then
        Decision = "Not eligible (assets > " & Format([MaxAssets], "$#,##0") & ")"
        Exit Function
    End If

    If UCase(Left(Current, 1)) = "Y" Then
        Decision = "Eligible for Supplimental Benefits"
    Else
        Decision = "Eligible for Basic Benefits"
    End If

End Function

[SamT]

So can you at least tell me what function to focus on using? I want to know in which direction to do my research in.

A formula(s) won't do the job. Research VBA: If... Then... Elses, Select Cases, Matrices, Class Modules, oh heck, just take an advanced class in VBA.

Seriously, the details are required in order to determine the direction to take.

On further thought, it might be possible to create a multibranch decision tree in Excel worksheets.

What I am envisioning is a sheet for each program with many small LookUps and LookUp tables that each return some decision. Under those, would be more that use the returned decisions to make more decisions.

All such processes, Excel Formulas or VBA, do require a detailed Decision Tree. Paul is asking for the details required to create said Tree.

We don't have that domain of Knowledge, you must provide it.

The mental excersise in creating a Decision Tree from scratch is strenuous, For each client class/type/situation you handle carefully notate each and every step in the process of deciding which programs they qualify for. you will find, on reviewing your notes, that you subconciously, or made them too quickly to notice, left out some mental steps. The written steps must be detailed and exact enough for an idiot computer to mechanically follow.

As an example of such subconscious decision you might make, if a client walks in, you don't consider any wheelchair programs. No computer is that observant.