PDA

View Full Version : Help with formula for scenarios



Lillian
08-29-2016, 11:53 AM
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
08-29-2016, 12:30 PM
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
08-29-2016, 12:56 PM
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
08-29-2016, 01:15 PM
But if I am familiar with all the regulations and rules, what would I need to do in order to set it up?

SamT
08-29-2016, 02:46 PM
Follow Paul's instructions for Each Program

Paul_Hossler
08-29-2016, 02:49 PM
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
09-01-2016, 06:58 AM
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
09-01-2016, 07:55 AM
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
09-01-2016, 09:14 AM
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.