Complex criteria to determine status

[jeffjam]

Hello, I would like to ask for a help with a simple algorithm I need to introduce. In the attached excel file there is a set of data with following notation:

Green cells - a line or total set by market
Yellow cells - a projected line or total
Blue cells - a final result or line - if positive value - home team (C column) lost, if negative - dome team won
Grey cells - a line or total difference (>=5 [5,6,7...999], >=7 [7,8,9...999], >=10 [10,11,12...999]) between line/total set by market and projected line/total
Red cells - cells that should contain the algorithm

Algorithm desrciption:

If the line/total difference (grey) is higher or equal to 5, the algorithm decides if projected line/total (yellow) beat the line set by market (green) comparing to the final result line/total (blue). If projected line/total beat the market line comparing to final result line/total, the algorithm should put W to the cells colored in red and the cell with W should be colored in green. If the projected line/total didnt beat the market line/total, it should put L to the cells colored in red and the cell with L should be colored in red. If neither projected or market line/total estimated final result line/total, it should put N to the cells colored in blue.

Example 1:

Game A
Market line - -5 (predicts home team to win by 5)
Projected line - -11 (predicts home team to win by 11)
Line difference is >=5
Final result - -6 (home team won by 6)
Even though market line -5 is closer to the final result -6 than predicted line -11, it should be marked as a W (with the cell colored in green) because it correctly predicted that -5 was too low regardless if the predicted line was -11 or -90.


Example 2:

Game B
Market line - 8 (predicts home team to lose by 8)
Projected line - 1 (predicts home team to lose by 1)
Line difference is >=5
Final result - 2 (home team lost by 2)
Even though the market line 8 is further away from the final result 2 than predicted line 1, it should be marked as a L (with the cell colored in red) because it incorrectly predicted that 8 was too high regardless if the predicted line was 1 or -10

Example 3:

Game C
Market line - -5 (predicts home team to win by 5)
Projected line - -11 (predicts home team to win by 11)
Line difference is >=5
Final result - 10 (home team lost by 10)

It should be marked as N (with the cell colored in blue) because neither market line or projected line predicted that home team would lost

Example 4:

Game D
Market line - -5 (predicts home team to win by 5)
Projected line - 3 (predicts home team to lose by 3)
Line difference is >=5
Final result - -3 (home team won by 3)

Even though market line -5 is closer to the final result line -3 than predicted line 3, it should be marked as a W (with the cell colored in green) because it correctly predicted that -5 was too high regardless if the predicted line was 3 or 90.


The same set of examples goes for totals.

I am not able to get this job done by simple functions and I would like to ask for a help to determine if this can be done by simple functions or some macro needs to be implemented. Thank you.

Hi, I reduced the file and added the described examples for line algorithm, however, I didn't add examples for totals because the logic is same. Only difference is that market total and projected total cannot be of negative value. I hope it is understandable.

[jeffjam]

Thanks Jerry for changing the tittle. Could someone, please, help me with this one?

[JBeaucaire]

You've created quite a lengthy ask here. Your reduced sheet isn't as reduced as it could be. Each of the scenarios outlined above should be demonstrated IN FULL with a couple of rows of example data each. That means each cell has sample data and fully mocked up sample answers. I do see you've tried to do some of that, but I admit that your sample data does not seem to match the verbal notes.

It is not uncommon for people to design spreadsheets that have information in it that is completely meaningful to the designer, but hard for anyone else to reconcile, so your help is needed on a row by row basis. For instance, the numbers in your reference cells seem to be positive and negative numbers, yet you refer to the values in those cells in ways that make the positive/negative hard to understand. I admit, I haven't read a single row example and understood how the verbal explanation matched the numbers.

In short, I don't get it. I am hoping there is simplty more context in your mind for that data than is properly represented in the data/explanation.

[MrShorty]

How much of this is limited to Excel, and how much of this is a broader logic/boolean algebra type question. Like Jerry, I quickly got overwhelmed by the volume of logic decisions that all of those descriptions called for.

Because there is a lot there, I did not try to engineer the whole thing. I might suggest, if you have not already done so, that you spend some time with the logic until you can express it in a systematic fashion. I think that, if I were going to engineer this, I would probably work out something like a truth table for each column (example of using truth tables, if you have not used them before http://planetmath.org/truthtable ). A table like this will allow you to identify each of your inputs, the different values and ranges of values that are important to your solution, then see what solution is supposed to go with each combination. Once you have a systematic analysis of the logic of the function, then it will be easier to program the function to reflect the desired logic.

As a start, looking down column M, I see several different inputs (though I have not gone as far as identifying all of the possible values each input can take): Market line, Predicted line, signed difference, unsigned difference, Home/Away. I didn't fully understand the logic, but Market line and Predicted line may only be important in figuring out the signed and unsigned difference, so you may not need separate columns in your truth table for those. Fill out a table with those inputs (and others that I may have missed) and the different possible values and combinations of values for each input. For each combination of conditions, determine the desired result.

That's probably how I would move forward, if I were to pursue this project. I would get a more systematic representation of the logic, then work out the final solution from that.

[jeffjam]

I admit that it is quite consfusing. I reduced the file to the bone, put description to comments and put desired results to the cells so it should be a little bit more ordered. I left only the four above examples in the file. Hope it helps, now it should be more easier to address any particular questions and problems.

[MrShorty]

Can we assume that you are familiar with the IF() function? the AND() function? It's still not clear to me how "market line" and "projected line" by themselves fit into the logic. What I see is:
column L =IF(ABS(G23-J23)<5,"",value_if_false)
column M =IF(ABS(G23-J23)<7,"",value_if_false)
column N =IF(ABS(G23-J23)<10,"",value_if_false)

If you put the threshold values in convenient cells (maybe at the top of the respective column), you could reduce those three into one formula =IF(ABS($G23-$J23)<L$1,"",value_if_false) [note how I used a mix of relative and absolute references so that the formula will copy across and down). I did not include anything for the value_if_false argument, since I do not see what you want to go into the cells if the difference is greater than threshold. I have also used the ABS() function on the difference, since it appears to me that the main thing you are after is the unsigned difference.

If there is more the the criteria, you can combine different logic functions [AND(), OR(), etc.] to make the criteria as complex as needed, or use another IF() function in either of the value_if_true or value_if_false arguments to string different logic tests together. This is where my suggestion for putting together a "truth table" of some sort might help -- to formalize your logic, then figure out how to fit into a series of IF() functions.

[jeffjam]

You are right, it is the "value if false" which is causing the trouble. Putting together a "truth table" could indeed help, however, I just can't find any logic behind possible outcomes. I put together all possible outcomes into the newly attached version, and also a try for a truth table but it doesn't work.

The very basic idea is this:

There is a game between team A and team B, the market predicts outcome which is formally put down as A(-5) vs. B(+5), meaning the team A is given a 5 point deficit before the game even started, and vica versa for the team B. Now, I make my own projections and predict that A team will win by 11 A(-11) vs. B(+11) which leads me to believe there is a value taking team A at -5 because I believe they will win by more than 5 points. Now, the game is over and the A team won by 6 [ A(-6) ] and I would like the algorithm to decide, that my projection was correct by putting down W.

All possible outcomes with descriptions are put down in the xls, however, I can't find the logic or the algorithm.