Support Board
Date/Time: Sun, 19 Jan 2025 06:37:36 +0000
Post From: Alert TICK-NYSE CROSSOVER causing error, returns array of 3 integers
| [2018-03-19 21:55:32] |
| Jeffrey - Posts: 2197 |
|
Short answer: Change your chart alert formula to use the Not Equal operator like: =OR(CROSSOVER(C,-800) <> 0, CROSSOVER(C,-1200) <> 0)
Or use CROSSFROMABOVE and/or CROSSFROMBELOW instead of CROSSOVER, if either of those matches your intent. Long answer: This error is expected and explained in the documentation. See the documentation for #VALUE! in Spreadsheet Errors. The AND() and OR() functions require boolean (TRUE/FALSE) expressions. For example, this Simple Alert formula will give a #VALUE! error: =OR(SG1, SG2). This formula will not give an error: =OR(SG1 <> 0,SG2 <> 0).
See the documentation for the OR Function: Returns #VALUE! if one of the given parameters could not be interpreted as a boolean value.
See the documentation for the CROSSOVER Function: Returns a value which indicates the type of crossing:
1 = The first Range crosses the second Range from above. -1 = The first Range crosses the second Range from below. 0 = The ranges do not cross each other. So in the case where the CROSSOVER function returns a value of -1, the OR function will result in a #VALUE! error, because a value of -1 has no meaning as a boolean value. Boolean values must be either 1 (TRUE) or 0 (FALSE). If you use CROSSOVER(...) <> 0, the Not Equal operator (<>) will result in 1 (TRUE) if there is a crossover in either direction, and 0 (FALSE) if there is no crossover. If you use the CROSSFROMABOVE or CROSSFROMBELOW functions, these will directly return boolean values. |