Predict likely outcome

[NFPeregrine]

I have an existing data set of approx 50K rows and 50 columns and i would like to use that data to predict the likely outcome of a new row.

As an example approx 30 columns contain information about patients, their names, sex, ages, illnesses, locations, occupations, insurance coverage etc.The remaining columns hold information about the outcome, eg how long they stayed in hostpital, if they developed depression, if they were off work, how long off work, if they died etc.

I would like to create a form that by entering information about a patient it looks up the existing data base and predicts the likely outcome for the new patient.

Eg a new patient is male, aged 40, suffers a heart attack and is a truck driver. and the model determines we have 60 previous examples the match the criteria, and of the 60 - 30% developed depression, 10% died, 80% were off work for 0-30 days and 20% were off work for 31-60 days.

I am a basic excel user and think i may have a complex problem.

 

[shg]

Welcome to the board.

I think your best bet is multiple linear regression for outcome as a function of the other data, and agree that you have a complex and non-trivial problem.

I'm sure insurance companies have this analysis down pat.

 

[NFPeregrine]

Can you please point me in a direction to get started. I was also searching YouTube with no luck.

 

[shg]

Google excel multiple linear regression. The first page of hits all look good at a glance to me.

 

[scottylad2]

deleyed

 

[scottylad2]

Predicting the future may be outwith Excels skillset, but from the data you have already collected you should be able to raise some stats.As your dealing with people, i'd seperate nto age categories as that will throw up different results, ie a 20 year old enetering hospital after chest pains may be out totally cured much earlier than an elderley person. Then seperate your sample points, ie all the defferent categories of reasons for entering hospital seperated by age then just fo basic percentages. A sample is all the people entering hospital with chest pains, split into Age categories



These are fairly simple well dfined histories of chest pain admissions and is only a couple of cattegories, you can make it much more refined by using more than one criteria per category. ie casualties in their 20' then split into m/f etc etc and from all that the returned results is what you would use to predict the future admissions and outcomes. Probability is only that though, but all your future staffing leves etc.....but you can't account for the unknown so at best it's an educated guess

You would then use charts to visually see where the trends are heading and create your form based on that
HTML generators not working

this answer makes no sense without the added tables but the html generators not working

 

[satoabe]

MLR hints...
Remember to pay attention to your R values as this is an indicator as to how well your model actually fits the distribution you are modeling.

A regression will indicate the relative impact the X variable(s) has upon your Y variable. (i.e. Y= average hospital stay duration, X1 = age, X2=sex, X3=insurance status, X4=smoker...etc so fx(Y)=b+b1X1+b2X2+b3X3+...bnXn+Error) Remember that you are modeling a distribution of data taken as a sample of the population and it will help you visulaize what the regression is telling you about the population. Also keep in mind that your sample may not reside on the population mean and could be more representative of tail data. It is always good to run your model on multiple sets of samples from the population to avoid Type 1 and Type 2 errors around your nul hypothesis.