Excel Probability Calculation

[Heffer Wolfe]

Hello everyone,

I've a huge problem in Excel and don't know how to deal with it. It's my first post in this forum, hope that someone can help me.

I added my database as an attachment. In the sheet A,B,C .. are same devices but different country. And the data in other columns are machine parts that have been used for this machine so far for repairing.

My aim is finding a probability for next possible part regarding the previous one based on the sheet that I added. For example if what is the probability of coming 46-270826P1 after 5399000-7?

I have no clue how to do these calculations. I only shared a small sample of my database.

I would be really really glad if you can help me about this urgent problem. Otherwise I dont think I can do that.

Thank you,

[Richard Buttrey]

Hi,

A lot is unclear.
Have you manually calculated an answer for the question you pose?
If so tell us how you arrive at the answer and maybe we can work out an Excel formula to produce that.

If not how are the two parts you mention connected? You used the phrase 'the previous one' which implied the row before the row containing one of the parts, but the two you mention are not adjacent.

Are you sure you really mean the probability which has a quite specific meaning?
Excel deals in facts. A part either exists in a list after or before another part number, and if it exists Excel can count the number of occurrencies before and after. Is your data population one that lends itself to statistical probabilities implying that in an extended list the same relation between two numbers continues to exist?

[Heffer Wolfe]

Hello Richard,

First of all, thank you for your interest and answer!

There is no way to calculate the probabilities manually since it has 224 machine part and 119 machine in total.

So, I created a sample which is attached and contains 9 machines (A,B,C,D...) and parts that have been used for these machines (96 duplicate, 81 unique values)

Based on only this sample and data, I am trying to understand the possible future part usage for each case. Connection is based on possibilities which is also related to adjacency. If 46-270826P1 is used then what will happen next means that possibilities of each other parts that will come after 46-270826P1?

I have no clue if it can be solved by Excel or other software.

Hope you can understand.

Thank you!

[Heffer Wolfe]

Is your data population one that lends itself to statistical probabilities implying that in an extended list the same relation between two numbers continues to exist?


For this question, I can say that that kind of pattern may exist in my extended database. I just wonder what might be the possibilities if we accept this sample database as our history.

[Richard Buttrey]

So in summary are you asking that for each of the unique part number cells in the tables, how many times does each other part number appear in the table after the unique part number? And by after do you mean both to the right and below or just below? This count value to be put in a first column adjacent to the unique part number list.

And then what about the second occurrence of a part number. Are all other part numbers after that one to be again subject to a count and this count placed in a second column adjacent to the unique part number list?

I fear however that you are into some serious number of calculations of the order of tens of thousands given your existing data which even if a macro is designed to process this it would take an extremely long time.

Maybe I've misunderstood the requirement but if not I'll be interested to see other responses here.

[Heffer Wolfe]

I assumed that if I can detect if Part B comes right after Part A more than once, that means that the possibility of occurence of this situation in future is higher than Part C comes after Part A.

That means that if a machine is repaired by using Part A, then Part B will be highly used for this machine rather than C since our history shows that.

It forces us to analyze each single cell with each other to detect it and I have no idea how to do it.

------------------------------------------------------------------------------------------------------------------------------
But the problem is I must also calculate Part B comes two, three steps after A right?

To be honest, I am really confused. Maybe we have to give weights to occurence distances than try to analyze possibilities.

This problem is way up to my level.

[MrShorty]

Opening disclaimer: I was never very good with probability problems. I will say that this sort of thing is a lot easier to program if you understand the math first. Since you seem unable to even explain the math you want to use, it makes it more difficult to help you program the math into Excel.

It seems to me, on its simplest level, this should just be a "counting" problem, shouldn't it?

1) Count the total number of A's.
2) For each A entry, count the number of times B immediately follows it.
3) Divide the result of 2 by the result of 1 -- this should be an estimate of the probability that B follows A.
4) Repeat 2 and 3 for each desired part #

So I ask you (and any other statistics savvy members) if that seems like a valid measure of the probability that B follows A.

[Heffer Wolfe]

Hello,

In a macro level, yes your explanation seems to me reasonable.

But in micro extent, this calculation only gives us a rough results, of course.

However, I think it will be an initiative move for this case. In this small sample I would be glad if we can figure it out how to do it.

Opening disclaimer: I was never very good with probability problems. I will say that this sort of thing is a lot easier to program if you understand the math first. Since you seem unable to even explain the math you want to use, it makes it more difficult to help you program the math into Excel.

It seems to me, on its simplest level, this should just be a "counting" problem, shouldn't it?

1) Count the total number of A's.
2) For each A entry, count the number of times B immediately follows it.
3) Divide the result of 2 by the result of 1 -- this should be an estimate of the probability that B follows A.
4) Repeat 2 and 3 for each desired part #

So I ask you (and any other statistics savvy members) if that seems like a valid measure of the probability that B follows A.

[MrShorty]

Here's how I'd probably start:

1) Put part # A (46-270826P1) in a cell. A simple =COUNTIF() function should be able to count the total occurances of A in the original table. http://office.microsoft.com/en-us/ex...077.aspx?CTT=1
=countif($A$1:$J$50,M2)

2) Put part # B (5399000-7) in and adjacent cell. Then use the =CONCATENATE() function to joint part # A with part # B into a single text string. http://office.microsoft.com/en-us/ex...085.aspx?CTT=1
=concatenate(m2,";",n2)

3) Build a helper table of concatenated values that will show part # A and the part # B immediately after. =concatenate(A2,";",A3) copied across and down to the same dimensions as original table.

4) another countif function to count the number of occurences of A;B. =countif($S$2:$AA$50,O2)

5) divide result of 4 by result of 1

[Heffer Wolfe]

Hey MrShorty;

Thanks for your all help. I really really appreciated it.

I would like to ask another question. What I've seen it, this formulation only calculates possibility ( or let says weight) of Part A then Part B together in all database.

But there are hundreds of different possibility for each part like Part A - then Part C, Part A - then Part K and so on.. Or completely different Part B - Part M, Part D - Part Z and so on.


Is there a shortcut way to calculate all these adjacency as well? Or Do I have to do it for each case manually as you have showed above.

Thank you so much again. I couldn't even think this way without you.

Heffer

Here's how I'd probably start:

1) Put part # A (46-270826P1) in a cell. A simple =COUNTIF() function should be able to count the total occurances of A in the original table. http://office.microsoft.com/en-us/ex...077.aspx?CTT=1
=countif($A$1:$J$50,M2)

2) Put part # B (5399000-7) in and adjacent cell. Then use the =CONCATENATE() function to joint part # A with part # B into a single text string. http://office.microsoft.com/en-us/ex...085.aspx?CTT=1
=concatenate(m2,";",n2)

3) Build a helper table of concatenated values that will show part # A and the part # B immediately after. =concatenate(A2,";",A3) copied across and down to the same dimensions as original table.

4) another countif function to count the number of occurences of A;B. =countif($S$2:$AA$50,O2)

5) divide result of 4 by result of 1

[MrShorty]

I would probably build another table of concatenated partnumber combinations. For this table:

1) left column contains a list of unique part numbers
2) top row contains the same list of unique part numbers
3) table contains =concatenate($A1,";",B$2). Note the use of mixed references so that the appropriate row and column references stay fixed.
4) Build the countif tables using the same structure, so you can use an appropriate combination of relative and absolute references in each formula
5) the final table should be the desired ratio.

[Heffer Wolfe]

I have analyzed your sheet. Sorry, but I couldn't get it your aim with this analysis. By the way, do we have to apply your first formula for each concatenation one by one manually?

Thank you again, seriously you are the best!

[shg]

Is the question you're trying to answer, "given a failure of part 123, is it likely that part 345 will subsequently fail?"

One would assume that certain failures result in replacement of several parts, and the repair parts get listed in no particular order, so the notion of 'subsequent' is a little sketchy with the data you have.

[MrShorty]

By the way, do we have to apply your first formula for each concatenation one by one manually?

With a good table structure and the right combination of relative and absolute references, you should only need to build the concatenate functions once, then copy and paste to fill out the table. If you are uncertain how to use relative and absolute references, I would suggest a quick review of http://office.microsoft.com/en-us/ex...es_in_formulas

And, shg makes a good point.

[Heffer Wolfe]

Yes, the main aim is detecting what you have stated, actually. I know it is a little sketchy but at least I would like to show that there is no such correlation in the database.

Is the question you're trying to answer, "given a failure of part 123, is it likely that part 345 will subsequently fail?"

One would assume that certain failures result in replacement of several parts, and the repair parts get listed in no particular order, so the notion of 'subsequent' is a little sketchy with the data you have.

I am not so familiar with absolute or relative reference structures, hope that it can be figured out :/

[MrShorty]

I am not so familiar with absolute or relative reference structures, hope that it can be figured out :/

IMO, understanding how to use relative and absolute references is a foundational principle in spreadsheet programming. If this is something you need to learn, I would suggest you spend some time with the help file I mentioned above and practice using them until you are comfortable with how they work.

[Heffer Wolfe]

One would assume that certain failures result in replacement of several parts, and the repair parts get listed in no particular order, so the notion of 'subsequent' is a little sketchy with the data you have.

Actually, I have transaction date of the parts. But I did not include it into this database, I sorted it from past to present (2011 - 2013) in this sample database. So the first part data for each machine is the first part that was used for this device.

[shg]

And only a single part is ever used to repair it?

[Heffer Wolfe]

Yes, only a single part
!