On 2002-08-30 00:41, jerryj wrote:
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.
On 2002-08-30 00:41, jerryj wrote:
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Target | Name | Value | Return | |||
2 | 0 | Ian0 | 42 | Ian1 | |||
3 | 40 | Ian1 | 169 | Ian13 | |||
4 | 50 | Ian2 | 23 | Ian0 | |||
5 | 60 | Ian3 | 157 | Ian12 | |||
6 | 70 | Ian4 | 78 | Ian4 | |||
7 | 80 | Ian5 | 103 | Ian7 | |||
8 | 90 | Ian6 | 72 | Ian4 | |||
9 | 100 | Ian7 | 116 | Ian8 | |||
10 | 110 | Ian8 | 181 | Ian15 | |||
11 | 120 | Ian9 | 231 | Ian20 | |||
12 | 130 | Ian10 | 94 | Ian6 | |||
13 | 140 | Ian11 | 136 | Ian10 | |||
14 | 150 | Ian12 | 150 | Ian12 | |||
15 | 160 | Ian13 | 107 | Ian7 | |||
16 | 170 | Ian14 | 157 | Ian12 | |||
17 | 180 | Ian15 | 210 | Ian18 | |||
18 | 190 | Ian16 | 218 | Ian18 | |||
19 | 200 | Ian17 | 95 | Ian6 | |||
20 | 210 | Ian18 | |||||
21 | 220 | Ian19 | |||||
22 | 230 | Ian20 | |||||
23 | 240 | Ian21 | |||||
Sheet1 |
On 2002-08-30 09:14, jerryj wrote:
Here it is!!
=IF(OR(V1<=V40,V1<V41),"The Eagle Has Landed",IF(AND(V1>V40,V1<=V41),"Philly Pride II",IF(AND(V1>V41,V1<=V42),"Egoholics",IF(AND(V1>V42,V1<=V43),"Hail Marys",IF(AND(V1>V43,V1<=V44),"Rice A Roni",IF(AND(V1>V44,V1<=V45),"Dallas Devestators",IF(AND(V1>V45,V1<=V46),"Packers2003",IF(AND(V1>V46,V1<=V47),"Clutch Ballers",IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes",IF(AND(V1>V48,V1<=V49),"Womping Wombats",IF(AND(V1>V49,V1<=V50),"Under Dogs",)))))))))))
On 2002-08-31 06:41, thereuare wrote:
Just adding a "work-around" that i've used that i believe is simpler than the above.
Split your IF statements between 2 cells, in your example you have 9 IF statements. In the first column use the first 7, but have the ultimate FALSE print an empty cell (ie- " ").
Then have a second column check the criteria on the other 2 IF statements, with the ultimate FALSE printing an empty cell.
Then in a 3rd column do an IF statement that displays the value of the cell that isn't empty (ie- =If(a1=" ", b1, a1) ).
This message was edited by thereuare on 2002-08-31 06:42