Spreadsheet: 879718d1727271818-problem-with-my-formula-formula-updated
Sheet: Ark1

First example is just with positive numbers, but still gives error in 4 cells |  |  |  |  |  |  |  |  | Second example, the formula chooses 40 for postive numbers, and error for negative numbers
-100 |  |  |  |  |  |  |  |  | -100 |  |  |  |  | [NAME]
-97 |  |  |  |  |  |  |  |  | -97 |  |  |  |  | J42>200;"40";J42=200;"40";J42>195;"39";J42=195;"39";J42>190;"38";J42>190;"38";J42>185;"37";J42=185;"37";J42>180;"36";J42=180;"36";J42>175;"35";J42=175;"35";J42>170;"34";J42=170;"34";J42>165;"33";J42=165;"33";J42>160;”32”;j42=160;”32”;J42>155;"31";J42=155;"31";J42>150;"30";J42=150;"30";J42>145;"29";J42=145;"29";J42>140;"28";J42=140;"28";J42>135;"27";J42=135;"27";J42>130;"26";J42=130;"26";J42>125;"25";J42=125;"25";J42>120;"24";J42=120;"24";J42>115;"23";J42=115;"23";J42>110;"22";J42=110;"22";J42>105;"21";J42=105;"21";J42>100;"20";J42=100;"20";J42>95;"19";J42=95;”19”;J42>90;"18";J42=90;"18";J42>85;"17";J42=85;"17";J42>80;"16";J42=80;"16";J42>75;"15";J42=75;"15";J42>70;"14";J42=70;"14";J42>65;"13";J42=65;"13";J42>60;"12";J42=60;"12";J42>55;"11";J42=55;"11";J42>50;"10";J42=50;"10";J42>45;"9";J42=45;"9";J42>40;"8";J42=40;"8";J42>35;"7";J42=35;"7";J42>30;"6";J42=30;"6";J42>25;"5";J42=25;"5";J42>20;"4";J42=20;"4";J42>15;"3";J42=15;"3";J42>10;"2";J42=10;"2";J42>5;"1";J42=5;"1";J42>0;"0";J42=0;”0”;J42>-5;"0";J42=-5;”-2”;J42>-10;"-2";J42=-10;”-4”;J42>-15;"-4";J42=-15;”-6”;J42>-20;"-6";J42=-20;”-8”;J42>-25;"-8";J42=-25;”-10”;J42>-30;"-10";J42=-30;”-12”;J42>-35;"-12";J42=-35;”-14”;J42>-40;"-14";J42=-40;”-16”;J42>-45;"-16";J42=-45;”-18”;J42>-50;"-18";J42=-50;”-20”;J42>-55;"-20";J42=-55;”-22”;J42>-60;"-22";J42=-60;”-24”;J42>-65;"-24";J42=-65;”-26”;J42>-70;"-26";J42=-70;”-28”;J42>-75;"-28";J42=-75;”-30”;J42>-80;"-30";J42=-80;”-32”;J42>-85;"-32";J42=-85;”-34”;J42>-90;"-34";J42=-90;”-36”;J42>-95;-36";J42=-95;”-38”;J42>-100;"-38";J42=-100;”-40”)
-95 |  |  |  |  |  |  |  |  | -95
-92 |  |  |  |  |  |  |  |  | -92
-90 |  |  |  |  |  |  |  |  | -90
-88 |  |  |  |  |  |  |  |  | -88
-85 |  |  |  |  |  |  |  |  | -85 |  |  |  |  | ”
-84 |  |  |  |  |  |  |  |  | -84 |  |  |  |  | "
-80 |  |  |  |  |  |  |  |  | -80
-76 |  |  |  |  |  |  |  |  | -76
-75 |  |  |  |  |  |  |  |  | -75
-72 |  |  |  |  |  |  |  |  | -72
-70 |  |  |  |  |  |  |  |  | -70
-67 |  |  |  |  |  |  |  |  | -67
-65 |  |  |  |  |  |  |  |  | -65
-61 |  |  |  |  |  |  |  |  | -61
-60 |  |  |  |  |  |  |  |  | -60
-58 |  |  |  |  |  |  |  |  | -58
-55 |  |  |  |  |  |  |  |  | -55
-52 |  |  |  |  |  |  |  |  | -52
-50 |  |  |  |  |  |  |  |  | -50
-46 |  |  |  |  |  |  |  |  | -46
-45 |  |  |  |  |  |  |  |  | -45
-41 |  |  |  |  |  |  |  |  | -41
-40 |  |  |  |  |  |  |  |  | -40
-36 |  |  |  |  |  |  |  |  | -36
-35 |  |  |  |  |  |  |  |  | -35
-32 |  |  |  |  |  |  |  |  | -32
-30 |  |  |  |  |  |  |  |  | -30
-27 |  |  |  |  |  |  |  |  | -27
-25 |  |  |  |  |  |  |  |  | -25
-21 |  |  |  |  |  |  |  |  | -21
-20 |  |  |  |  |  |  |  |  | -20
-16 |  |  |  |  |  |  |  |  | -16
-15 |  |  |  |  |  |  |  |  | -15
-12 |  |  |  |  |  |  |  |  | -12
-10 |  |  |  |  |  |  |  |  | -10
-7 |  |  |  |  |  |  |  |  | -7
-5 |  |  |  |  |  |  |  |  | -5
-2 |  |  |  |  |  |  |  |  | -2
0 |  |  |  |  |  |  |  |  | 0
2 |  |  |  |  |  |  |  |  | 2
5 |  |  |  |  |  |  |  |  | 5
7 |  |  |  |  |  |  |  |  | 7
10 |  |  |  |  |  |  |  |  | 10
12 |  |  |  |  |  |  |  |  | 12
15 |  |  |  |  |  |  |  |  | 15
16 |  |  |  |  |  |  |  |  | 16
20 |  |  |  |  |  |  |  |  | 20
23 |  |  |  |  |  |  |  |  | 23
25 |  |  |  |  |  |  |  |  | 25
26 |  |  |  |  |  |  |  |  | 26
30 |  |  |  |  |  |  |  |  | 30
31 |  |  |  |  |  |  |  |  | 31
35 |  |  |  |  |  |  |  |  | 35
36 |  |  |  |  |  |  |  |  | 36
40 |  |  |  |  |  |  |  |  | 40
41 |  |  |  |  |  |  |  |  | 41
45 |  |  |  |  |  |  |  |  | 45
46 |  |  |  |  |  |  |  |  | 46
50 |  |  |  |  |  |  |  |  | 50
51 |  |  |  |  |  |  |  |  | 51
55 |  |  |  |  |  |  |  |  | 55
56 |  |  |  |  |  |  |  |  | 56
60 |  |  |  |  |  |  |  |  | 60
61 |  |  |  |  |  |  |  |  | 61
65 |  |  |  |  |  |  |  |  | 65
67 |  |  |  |  |  |  |  |  | 67
70 |  |  |  |  |  |  |  |  | 70
73 |  |  |  |  |  |  |  |  | 73
75 |  |  |  |  |  |  |  |  | 75
76 |  |  |  |  |  |  |  |  | 76
80 |  |  |  |  |  |  |  |  | 80
83 |  |  |  |  |  |  |  |  | 83
85 |  |  |  |  |  |  |  |  | 85
86 |  |  |  |  |  |  |  |  | 86
90 |  |  |  |  |  |  |  |  | 90
92 |  |  |  |  |  |  |  |  | 92
95 |  |  |  |  |  |  |  |  | 95
96 |  |  |  |  |  |  |  |  | 96
100 |  |  |  |  |  |  |  |  | 100
102 |  |  |  |  |  |  |  |  | 102
105 |  |  |  |  |  |  |  |  | 105
107 |  |  |  |  |  |  |  |  | 107
110 |  |  |  |  |  |  |  |  | 110
111 |  |  |  |  |  |  |  |  | 111
115 |  |  |  |  |  |  |  |  | 115
116 |  |  |  |  |  |  |  |  | 116
120 |  |  |  |  |  |  |  |  | 120
123 |  |  |  |  |  |  |  |  | 123
125 |  |  |  |  |  |  |  |  | 125
126 |  |  |  |  |  |  |  |  | 126
130 |  |  |  |  |  |  |  |  | 130
131 |  |  |  |  |  |  |  |  | 131
135 |  |  |  |  |  |  |  |  | 135
136 |  |  |  |  |  |  |  |  | 136
140 |  |  |  |  |  |  |  |  | 140
141 |  |  |  |  |  |  |  |  | 141
145 |  |  |  |  |  |  |  |  | 145
146 |  |  |  |  |  |  |  |  | 146
150 |  |  |  |  |  |  |  |  | 150
151 |  |  |  |  |  |  |  |  | 151
155 |  |  |  |  |  |  |  |  | 155
156 |  |  |  |  |  |  |  |  | 156
160 |  |  |  |  |  |  |  |  | 160
161 |  |  |  |  |  |  |  |  | 161
165 |  |  |  |  |  |  |  |  | 165
166 |  |  |  |  |  |  |  |  | 166
170 |  |  |  |  |  |  |  |  | 170
172 |  |  |  |  |  |  |  |  | 172
175 |  |  |  |  |  |  |  |  | 175
176 |  |  |  |  |  |  |  |  | 176
180 |  |  |  |  |  |  |  |  | 180
181 |  |  |  |  |  |  |  |  | 181
185 |  |  |  |  |  |  |  |  | 185
186 |  |  |  |  |  |  |  |  | 186
190 |  |  |  |  |  |  |  |  | 190
193 |  |  |  |  |  |  |  |  | 193
195 |  |  |  |  |  |  |  |  | 195
197 |  |  |  |  |  |  |  |  | 197
200 |  |  |  |  |  |  |  |  | 200
201 |  |  |  |  |  |  |  |  | 201
205 |  |  |  |  |  |  |  |  | 205