LinkBack URL
About LinkBacks
This one has been pretty challenging for me. Feel free to try it out if you have the time and patience.
Explanation: This is dealing with a strategic attempt at the prediction of a random event. This deals mainly with the "timing" of the strategic attempt. As opposed to attempting a prediction on every occurrence of the event, I will be attempting to make a prediction only when I have the best chance of success.
Please note that this calculation has nothing to do with probability or statistics per se. It's merely setting up specific rules for when an attempt will be made at a prediction. It does not deal with predicting the event itself.
Rules: The event has one of 2 outcomes, EVENT SUCCESS or EVENT FAILED. It is helps, you could say ES or EF for short.
I have 2 columns. The first column will contain a randomly ordered list of ES or EF. The second will contain the calculation and the output will be PREDICTION SUCCESS, PREDICTION FAILED, or PAUSE. PS and PF for short and simply the whole word PAUSE.
Mechanics: The second column would be referencing the first column in order to return a result. The overall idea is to remain on PAUSE for normal, random results that don't show the specific pattern that I'm looking for (meaning that PAUSE would be displayed as the output in the second column). However, once the pattern occurs, then, based on the input within the first column, I would want an output of either PREDICTION SUCCESS or PREDICTION FAILED (PS or PF). At this point, after two close together inputs of EVENT SUCCESS (ES) in the first column, the formula would yield an output of PREDICTION SUCCESS (PS) in the second. The same input of EVENT FAILED (EF) in the first column would yield an output of PREDICTION FAILED (PF) in the second column.
The pattern that I would be looking for is a long string of consecutive EVENT FAILED (EF) in the first column. After that point, if the input in the first column is EVENT SUCCESS (ES) x2, then the output in the second column would be PREDICTION SUCCESS (PS). If the input in the first column would be EVENT FAILED (EF) x2, then the output of the second column would be PREDICTION FAILED (PF).
Once predictions have started, the next thing needed is a mechanism for "stopping" and going back to the PAUSED state. This should happen when a certain consecutive number of EVENT FAILED (EF) occurs in the first column, therefore creating PREDICTION FAILED (PF) in the second column--occurring without any new EVENT SUCCESS / PREDICTION SUCCESS (ES / PS) within a specified range. On the other hand, new occurrences PREDICTION SUCCESS (PS) within the specified range will allow the prediction to continue. Lastly, once the EVENT SUCCESS / PREDICTION SUCCESS (ES / PS) has run slowed sufficiently to where there is a certain consecutive number of PREDICTION FAILED (PF) within the specified range, then PAUSE is displayed.
From here, the entire process should start over. I have been working on this and this is actually where I have the most trouble. PAUSE should be displayed until there is no EVENT SUCCESS (ES) input in column one. The process of displaying PREDICTION SUCCESS (PS) or PREDICTION FAILED (PF) should not resume again until a long string of EVENT FAILED (EF) has occurred once again.
One idea that just came to mind is putting the EVENT SUCCESS (ES) and EVENT FAILED (EF) formula alone in second column and then, beside it, placing the PREDICTION SUCCESS and PREDICTION FAILED in a third column--having the second column reference the first the third referencing the second. I have been using only two columns so far.
Summary of Specifics: This formula should be one that's descending in its procession within the column. By that I mean new values will be added regularly and the new values will be added at the top of the spreadsheet and the formula should be copied/dragged and dropped upward, moving toward row 1.
Currently, the formula that I have been trying to get in working order, which is the one where calculations for PS, and PF are all in the same column, consists of two ranges which are adjacent to each other, top against bottom. The top range is currently referencing 6 rows and the bottom which is referencing the next 5 rows below that. When referring to both ranges combined, I call them the full range, 11 rows bottom to top.
Starting from the very beginning... After a sufficiently long string of EVENT FAILED has occurred in the first column, the full range will consist of nothing but consecutive EVENT FAILED, at which time the PAUSE will still be the output. In continuing, there may be more EVENT FAILED in the first column, and this will mean a continued output of PAUSE.
The next thing to eventually happen will be that an EVENT SUCCESS will occur. PAUSE still occurs on that very result, but as can be expected, another EVENT SUCCESS should soon occur while the first EVENT SUCCESS is still within the range of reference for the top-most portion of the full range which, as stated above, is referencing 6 rows.
The idea is that so long as there are two EVENT SUCCESS inputs in the first column that are referenced within this top-most range, the outputs for the second column will be PREDICTION SUCCESS or PREDICTION FAILED, based on what's in the first column on any corresponding row. Since the direction of the formula is descending, moving toward the top of the spreadsheet, the results will be ascending as they are added.
Eventually comes the time when there is no EVENT SUCCESS input being referenced in column one by the formula in column two. This means that 6 consecutive EVENT FAILED would have come after it. This is when PAUSE is resumed. The part that I have had difficulty with is figuring out how to keep it that way until the next long string of EVENT FAILED and both the top-most and bottom-most ranges are both referencing nothing but consecutive EVENT FAILED.
The main purpose of the first range is to help know when to leave the PAUSED state and when to give an output of PREDICTION SUCCESS or PREDICTION FAILED and also to know exactly when to resume the PAUSED state. The purpose of the bottom-most reference is to help maintain the PAUSED state until the correct criteria is met. The criteria being the 6 top rows and the bottom-most row, the one in question which references 5 rows, are referencing nothing but consecutive PREDICTION FAILED.
To get a clearer idea of all this, please see the following formula. It's what I have so far but can't quit get to work:
IF(AND(EXACT(B31,"EVENT SUCCESS"),EXACT(COUNTIF(B31:B36,"EVENT SUCCESS"),1),EXACT(COUNTIF(B37:B41,"EVENT FAILED"),5)),"PAUSE",
IF(AND(EXACT(B36,"EVENT SUCCESS"),EXACT(COUNTIF(B31:B36,"EVENT SUCCESS"),1),EXACT(COUNTIF(B37:B41,"EVENT FAILED"),5)),"PAUSE",
IF(AND(EXACT(B31,"EVENT SUCCESS"),COUNTIF(B31:B36,"EVENT SUCCESS")>1,EXACT(COUNTIF(B37:B41,"EVENT FAILED"),5)),"PREDICTION SUCCESS",
IF(AND(NOT(EXACT(B31,"EVENT SUCCESS")),COUNTIF(B31:B36,"EVENT SUCCESS")>0,EXACT(COUNTIF(B37:B41,"EVENT FAILED"),5),EXACT(V12,"EVENT FAILED")),"PREDICTION FAILED",
IF(AND(NOT(EXACT(B31,"EVENT SUCCESS")),EXACT(COUNTIF(B31:B36,"EVENT SUCCESS"),1),EXACT(COUNTIF(B37:B41,"EVENT SUCCESS"),1)),"PAUSE",
IF(AND(EXACT(B36,"EVENT SUCCESS"),EXACT(COUNTIF(B31:B36,"EVENT FAILED"),5),COUNTIF(B37:B41,"EVENT SUCCESS")>1),"PAUSE",
IF(AND(EXACT(B31,"EVENT SUCCESS"),COUNTIF(B31:B36,"EVENT SUCCESS")>1,COUNTIF(B37:B41,"EVENT SUCCESS")>1),"PREDICTION SUCCESS",
IF(AND(NOT(EXACT(B31,"EVENT SUCCESS")),COUNTIF(B31:B36,"EVENT SUCCESS")>1,COUNTIF(B37:B41,"EVENT SUCCESS")>1),"PREDICTION FAILED","PAUSE"
))))))))
This post can also be found at the following locations:
https://www.mrexcel.com/forum/excel-...dom-event.html
http://www.excelguru.ca/forums/showt...A-Random-Event
https://answers.yahoo.com/question/i...4204137AAFignP
No solutions have been yet given at any of the above locations.
Register To Reply
Bookmarks