Greetings all -
I am stumped on how to accomplish this and would be very grateful for any help or suggestions, thank you!
1 - I have rows with prices in them in a table: open, high, low, close (cols ABCD)
2 - Col E has a formula that identifies a condition. For simplicity's sake, I am hand entering a text string of "SC" (satisfies criteria) for this example worksheet.
3- Col F records the high price of the row (col B) if ColE = "SC".
4- Col G is a running count of the price (col F) last triggered by Col E's "SC". When a new "SC" is triggerred, Col G is updated with that new price.
5- If the high (col B) exceeds the value listed in Col G, then Col H should return "condition met", and if not, it is left blank.
A precondition for this to calculate properly is:
One issue I have identified, is that the high (col B) of the row before must be below an SC value before the next row can (possibly) exceed it. This ensures that an SC value that has already been exceeded is not counted as exceed again for rows (or even the very next row) that starts above an SC value already encountered.
A requirement:
Since this a table, that the solution still function if the table is sorted.
Example:
Once SC Val run price (col G) has been used (exceeded), subsequent rows now need to validate against the (now) nearest, unused SC Val run price. This would come from an unused price in col F.
In this case, row four is the first row that satisfies the criteria, so the price (SC value) is stored in F4. It also now constitutes the first running value in G4.
In row 8, the high price exceeds the SC Val run (G6, the stored 99.83), thus "condition met" in H8.
In row 15, the criteria has been satisfied, so a new SC Value (85.00, F15) has been stored. It happens to be below the the last used SC Value. It satisfies the condition in H17, even though it satisfies it at a lower price.
Now— Please note that in F9, there is a stored price (that did satisfy the criteria), but no high price from col B9-B18 so far has exceeded it.
**What I need is to have the NEAREST, UNUSED, price to become the data for col G18.
Why G18? Because the bar above it has had the condition met, so this is now a used price. I now need to supply the nearest UNUSED price above, in col G18 for subsequnt rows to test against.
Question one: how do you discard a price that has now been used in a running column, and get the next nearest unsused price above into that column? An unused SC value run (col G) could be above or below the most recently exceeded one.
Question two: it is certainly possible that a single high price could exceed several SC values in one shot. If it does, again, how to eliminate all those now "used" prices in favor of the (now) most recent, unused SC value run price?
Question three: It is also certainly possible that in 2000 rows, an SC value could be the exact same as some value used before, which could still be a now legitimate new value to test against. So there needs to be some way to reset what is considered used vs. something that (even if at the exact same price as before) is considered a new value to test against. Reset by number of rows? Days? A range of high to low prices?
I hope the above description makes sense:
It is (very) possible that running column is not the way to achieve this. It is also possible that I am either overthinking the problem, or the opposite, missing additional issues.
Thanks so much for the help!
I am stumped on how to accomplish this and would be very grateful for any help or suggestions, thank you!
1 - I have rows with prices in them in a table: open, high, low, close (cols ABCD)
2 - Col E has a formula that identifies a condition. For simplicity's sake, I am hand entering a text string of "SC" (satisfies criteria) for this example worksheet.
3- Col F records the high price of the row (col B) if ColE = "SC".
4- Col G is a running count of the price (col F) last triggered by Col E's "SC". When a new "SC" is triggerred, Col G is updated with that new price.
5- If the high (col B) exceeds the value listed in Col G, then Col H should return "condition met", and if not, it is left blank.
A precondition for this to calculate properly is:
One issue I have identified, is that the high (col B) of the row before must be below an SC value before the next row can (possibly) exceed it. This ensures that an SC value that has already been exceeded is not counted as exceed again for rows (or even the very next row) that starts above an SC value already encountered.
A requirement:
Since this a table, that the solution still function if the table is sorted.
Example:
Once SC Val run price (col G) has been used (exceeded), subsequent rows now need to validate against the (now) nearest, unused SC Val run price. This would come from an unused price in col F.
In this case, row four is the first row that satisfies the criteria, so the price (SC value) is stored in F4. It also now constitutes the first running value in G4.
In row 8, the high price exceeds the SC Val run (G6, the stored 99.83), thus "condition met" in H8.
In row 15, the criteria has been satisfied, so a new SC Value (85.00, F15) has been stored. It happens to be below the the last used SC Value. It satisfies the condition in H17, even though it satisfies it at a lower price.
Now— Please note that in F9, there is a stored price (that did satisfy the criteria), but no high price from col B9-B18 so far has exceeded it.
**What I need is to have the NEAREST, UNUSED, price to become the data for col G18.
Why G18? Because the bar above it has had the condition met, so this is now a used price. I now need to supply the nearest UNUSED price above, in col G18 for subsequnt rows to test against.
Question one: how do you discard a price that has now been used in a running column, and get the next nearest unsused price above into that column? An unused SC value run (col G) could be above or below the most recently exceeded one.
Question two: it is certainly possible that a single high price could exceed several SC values in one shot. If it does, again, how to eliminate all those now "used" prices in favor of the (now) most recent, unused SC value run price?
Question three: It is also certainly possible that in 2000 rows, an SC value could be the exact same as some value used before, which could still be a now legitimate new value to test against. So there needs to be some way to reset what is considered used vs. something that (even if at the exact same price as before) is considered a new value to test against. Reset by number of rows? Days? A range of high to low prices?
I hope the above description makes sense:
It is (very) possible that running column is not the way to achieve this. It is also possible that I am either overthinking the problem, or the opposite, missing additional issues.
Thanks so much for the help!
| The FO Q.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Open Price | High Price | Low Price | Close price | Satisfies Criteria | SC Value | SC Val Run | Condition Met | ||
| 2 | (formula to satisfy criteria) | |||||||||
| 3 | 100.00 | 82.00 | 99.58 | 99.68 | 0.00 | |||||
| 4 | 99.67 | 99.83 | 98.58 | 98.81 | SC | 99.83 | 99.83 | |||
| 5 | 98.82 | 99.12 | 98.64 | 98.88 | 99.83 | |||||
| 6 | 98.87 | 99.00 | 97.78 | 98.90 | 99.83 | |||||
| 7 | 98.91 | 99.60 | 98.86 | 99.74 | 99.83 | |||||
| 8 | 99.76 | 101.51 | 99.73 | 100.58 | 99.83 | condition met | ||||
| 9 | 100.58 | 101.78 | 99.83 | 100.06 | SC | 101.78 | 101.78 | |||
| 10 | 100.05 | 100.37 | 98.38 | 98.54 | 101.78 | |||||
| 11 | 98.54 | 99.68 | 98.42 | 99.41 | 101.78 | |||||
| 12 | 99.41 | 101.00 | 98.85 | 100.58 | 101.78 | |||||
| 13 | 100.56 | 91.00 | 99.25 | 99.95 | 101.78 | |||||
| 14 | 99.96 | 84.00 | 99.65 | 99.71 | 101.78 | |||||
| 15 | 90.00 | 85.00 | 89.75 | 90.25 | SC | 85.00 | 85.00 | |||
| 16 | 99.55 | 84.90 | 98.86 | 99.07 | 85.00 | |||||
| 17 | 84.90 | 86.00 | 84.50 | 85.75 | 85.00 | condition met | ||||
| 18 | 85.75 | 86.26 | 83.00 | 83.20 | 85.00 | |||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3:F18 | F3 | =IF(E3="SC",B3,"") |
| G3:G18 | G3 | =IF(F3="",G2,F3) |
| H3:H18 | H3 | =IF(AND(B2<G3,B3>G3), "condition met","") |
