Macro for finding a relationship/patterns for excel forecasting and prediction.

shuga

New Member
Joined
Aug 25, 2015
Messages
30
I am wondering if its possible to write a macro that can find a relationship between columns in the worksheet found on the link below and make possible forecasts based on the relationship.

https://www.dropbox.com/s/06hpjgcxuh61vkg/FIND A RELATIONSHIP.xlsx?dl=0

The worksheet has two results. Pass or Fail and what i would like to achieve are 3 things as follows:

(1) Use macro to scan the worksheet and see if a pattern or relationship exists between the columns.

(2) Using the relationship or pattern to forecast and highlight in color which rows on Column T the word PASS will appear.

(3) List the top one hundred rows of 8 number combination that fall under 'PASS'. e.g 4,13,21,24,34,36,45,48. 7,8,21,22,23,24,25,26. 45,49,22,46,34,18,32,35. etc

( i already have the results for column T, i just need a macro that can scan worksheets, determine relationships between each column and make possible forecasts and then i will compare its accuracy)

It is my sincerest hope an expert here will help me achieve this feat.

Thanking you in advance.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
Replace PASS by 1, FAIL by 0 and do a correlation on all of the columns.

If any correlation(s) seem satisfactory for you, use standard regression (LINEST) and regression prediction to get the rest of what you ask.
 

shuga

New Member
Joined
Aug 25, 2015
Messages
30
Thanks for the reply but i wish it was that easy.
I did a correleation on the columns and i just couldnt understand the results or what to do with it.
 

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
Thanks for the reply but i wish it was that easy.
I did a correleation on the columns and i just couldnt understand the results or what to do with it.
It looks like your PASS and FAIL are pretty randomly scattered.
If they're truly random then any set of random cells showing PASS in Col T will have equally probability to any other set.
So I assume you must have some reason for supposing they're not randomly scattered. Maybe you hypothesize that some rows have greater probability of having PASS than other rows.
To attempt to give a sensible answer it would help to know the process by which the PASS and FAIL are generated. If you think it's not a totally random process then why do you think that, and what do you think it is? Why should there be any pattern?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,772
Messages
5,470,692
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top