quantmaven
New Member
- Joined
- May 16, 2018
- Messages
- 11
Hi,
How can I make a more intelligent formula that does this:
If any data in the previous row is out of the boundaries, data in next row (yellow) resets back to its threshold? I made an example with two columns that work but it's poorly written and it would become a nightmare with many columns. I feel an array must be used but I'm not sure how to do it. Thanks!
How can I make a more intelligent formula that does this:
If any data in the previous row is out of the boundaries, data in next row (yellow) resets back to its threshold? I made an example with two columns that work but it's poorly written and it would become a nightmare with many columns. I feel an array must be used but I'm not sure how to do it. Thanks!
Reset_Value.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | RESET VALUE | 50% | 50% | ||
2 | HI BOUND | 60% | 60% | ||
3 | LO BOUND | 40% | 40% | ||
4 | |||||
5 | DATE | ABC | XYZ | ||
6 | 2012-12-31 | 61.0% | 39.0% | ||
7 | 2013-01-31 | 50.0% | 50.0% | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C6 | C6 | =1-B6 |
B7:C7 | B7 | =IF(OR($B6>$B$2,$B6<$B$3,$C6>$C$2,$C6<$C$3),B1,B6) |