IF statement

[CThai]

Hello -

I'm tying to finds the most recent X or O. Then takes the price on that day and compares it to the current price and based on the difference either higher or lower puts out an X if the current price is higher and an O if the current price is lower by the Half StartData - however when i get to about 6 IF statement it freeze up and it wont give me the X or O's

please help!!

   G     H        I        J  K  L  M  N  O  P  Q  R  S     
71 23.91 2.200502 1.100251 6                          O     
72 24.17 2.200502 1.100251 7        O                       
73 25.49 2.200502 1.100251 8           O              X     
74 25.29 2.200502 1.100251 9        X     O           FALSE 
75 25.48 2.200502 1.100251 10          X     O        FALSE 
76 26.06 2.200502 1.100251 11             X     O     FALSE 
77 25.92 2.200502 1.100251 12                X     O  FALSE 
78 27.38 2.200502 1.100251 13                   X     X     
79 27.29 2.200502 1.100251 14       X              X  FALSE 
80 27.41 2.200502 1.100251 15          X              FALSE 
81 26.41 2.200502 1.100251 16             X           FALSE 
82 24.69 2.200502 1.100251 17                X        O     
83 25.53 2.200502 1.100251 18       O           X     FALSE 
84 25.71 1.535899 0.767949 19 19       O           X  X     
85 25.84 1.535899 0.767949 1        X     O           FALSE 
86 26.86 1.535899 0.767949 2           X     O        X     

S&R spotting

[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
I71:I86 =H71/2
J71:J86 =IF(C71=C70,J70+1,1)
K71:K86 =IF(J72=1,J71," ")
M71:M86 =IF($S70="X","X",IF($S70="O","O"," "))
N71:N86 =IF($S69="X","X",IF($S69="O","O"," "))
O71:O86 =IF($S68="X","X",IF($S68="O","O"," "))
P71:P86 =IF($S67="X","X",IF($S67="O","O"," "))
Q71:Q86 =IF($S66="X","X",IF($S66="O","O"," "))
R71:R86 =IF($S65="X","X",IF($S65="O","O"," "))
S73     =IF(OR(M73="O",M73="X"),IF(G73-G72>=I73,"X",IF(G72-G73>=I73,"O")),IF(OR(N73="O",N73="X"),IF(G73-G71>=I73,"X",IF(G71-G73>=I73,"O")),IF(OR(O73="O",O73="X"),IF(G73-G70>=I73,"X",IF(G70-G73>=I73,"O")),IF(OR(P73="O",P73="X"),IF(G73-G69>=I73,"X",IF(G69-G73>=I73,"O")),IF(OR(Q73="O",Q73="X"),IF(G73-G68>=I73,"X",IF(G68-G73>=I73,"O")),IF(OR(R73="O",R73="X"),IF(G73-G67>=I73,"X",IF(G67-G73>=I73,"O"))," "))))))
S74     =IF(OR(M74="O",M74="X"),IF(G74-G73>=I74,"X",IF(G73-G74>=I74,"O")),IF(OR(N74="O",N74="X"),IF(G74-G72>=I74,"X",IF(G72-G74>=I74,"O")),IF(OR(O74="O",O74="X"),IF(G74-G71>=I74,"X",IF(G71-G74>=I74,"O")),IF(OR(P74="O",P74="X"),IF(G74-G70>=I74,"X",IF(G70-G74>=I74,"O")),IF(OR(Q74="O",Q74="X"),IF(G74-G69>=I74,"X",IF(G69-G74>=I74,"O")),IF(OR(R74="O",R74="X"),IF(G74-G68>=I74,"X",IF(G68-G74>=I74,"O")),"  "))))))
S75:S86 =IF(OR(M75="O",M75="X"),IF(G75-G74>=I75,"X",IF(G74-G75>=I75,"O")),IF(OR(N75="O",N75="X"),IF(G75-G73>=I75,"X",IF(G73-G75>=I75,"O")),IF(OR(O75="O",O75="X"),IF(G75-G72>=I75,"X",IF(G72-G75>=I75,"O")),IF(OR(P75="O",P75="X"),IF(G75-G71>=I75,"X",IF(G71-G75>=I75,"O")),IF(OR(Q75="O",Q75="X"),IF(G75-G70>=I75,"X",IF(G70-G75>=I75,"O")),IF(OR(R75="O",R75="X"),IF(G75-G69>=I75,"X",IF(G69-G75>=I75,"O"))," "))))))

[Table-It] version 09 by Erik Van Geit

 

[Misca]

As far as I know, the IF-function is limited to max 7 nested IF-statements.

The best way (and the only that I can come up with) to get around this limitation would be to use VBA & write either a macro or your own function using either Select Case or several IF-statements.

 

[CThai]

Hi Misca

do you know where i can go and find examples? I'm not really good at vba ( beginners)

 

[sanrv1f]

I am not able to understand your requirement, could you explain each column in the data and the expected results? where is the current price located? what is HalfstartDAta?

 

[CThai]

hi sanrv1f

in Column G i have the total Data, column H i have StartData, and column I is half of the startdata (ColumnH/2)

what I'm tying to get is for the IF-Statement to put an X or an O --- take the price on the day (total Data) compare it to the current price and based on the different put an X or an O --- X if the current price is higher and O if the current price is lower from Column I


below is my if statement but it wont allow me do more then 6

=IF(OR(M74="O",M74="X"),IF(G74-G73>=I74,"X",IF(G73-G74>=I74,"O")),IF(OR(N74="O",N74="X"),IF(G74-G72>=I74,"X",IF(G72-G74>=I74,"O")),IF(OR(O74="O",O74="X"),IF(G74-G71>=I74,"X",IF(G71-G74>=I74,"O")),IF(OR(P74="O",P74="X"),IF(G74-G70>=I74,"X",IF(G70-G74>=I74,"O")),IF(OR(Q74="O",Q74="X"),IF(G74-G69>=I74,"X",IF(G69-G74>=I74,"O")),IF(OR(R74="O",R74="X"),IF(G74-G68>=I74,"X",IF(G68-G74>=I74,"O")),"  "))))))

 

[CThai]

help... any suggestion?

 

[CThai]

ok i hope i can explain this better then the last 2 post... my apologize

what i'm tying to do is have a macro to run the IF statements since excel only allow 7(?) IF statement...

the IF statement below is looking at column G73, take G73 - G72 if that is greater or equal to I73 then put an X, IF G72 - G73 greater or equal to I73 put an O and if they are not greater or equal then FALSE

=IF(OR(M73="O",M73="X"),IF(G73-G72>=I73,"X",IF(G72-G73>=I73,"O"))

,IF(OR(N73="O",N73="X"),IF(G73-G71>=I73,"X",IF(G71-G73>=I73,"O"))

,IF(OR(O73="O",O73="X"),IF(G73-G70>=I73,"X",IF(G70-G73>=I73,"O"))

,IF(OR(P73="O",P73="X"),IF(G73-G69>=I73,"X",IF(G69-G73>=I73,"O"))

,IF(OR(Q73="O",Q73="X"),IF(G73-G68>=I73,"X",IF(G68-G73>=I73,"O"))

,IF(OR(R73="O",R73="X"),IF(G73-G67>=I73,"X",IF(G67-G73>=I73,"O"))," "))))))

again my apologize if this look messy

 

[sanrv1f]

still I dont understand what you are trying, any how, see if the below comes anywhere closer to what you need

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S
71	23.91	2.200502	1.100251	1									O
72	24.17	2.200502	1.100251	2			O						O
73	25.49	2.200502	1.100251	3			O	O					X
74	25.29	2.200502	1.100251	4			X	O	O				O
75	25.48	2.200502	1.100251	5			O	X	O	O			O
76	26.06	2.200502	1.100251	6			O	O	X	O	O		O
77	25.92	2.200502	1.100251	7			O	O	O	X	O	O	O
78	27.38	2.200502	1.100251	8			O	O	O	O	X	O	X
79	27.29	2.200502	1.100251	9			X	O	O	O	O	X	O
80	27.41	2.200502	1.100251	10			O	X	O	O	O	O	O
81	26.41	2.200502	1.100251	11			O	O	X	O	O	O	O
82	24.69	2.200502	1.100251	12			O	O	O	X	O	O	O
83	25.53	2.200502	1.100251	13			O	O	O	O	X	O	O
84	25.71	1.535899	0.76795	14			O	O	O	O	O	X	O
85	25.84	1.535899	0.76795	15			O	O	O	O	O	O	O
86	26.86	1.535899	0.76795	16			O	O	O	O	O	O	X
Sheet1

Excel 2003

Cell Formulas
Range	Formula
I71	=H71/2
J71	=IF(C71=C70,J70+1,1)
K71	=IF(J72=1,J71," ")
M71	=IF(OR(OFFSET($S71,-COLUMNS($M71:M71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:M71),)," ")
M72	=IF(OR(OFFSET($S72,-COLUMNS($M72:M72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:M72),)," ")
N71	=IF(OR(OFFSET($S71,-COLUMNS($M71:N71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:N71),)," ")
N72	=IF(OR(OFFSET($S72,-COLUMNS($M72:N72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:N72),)," ")
O71	=IF(OR(OFFSET($S71,-COLUMNS($M71:O71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:O71),)," ")
O72	=IF(OR(OFFSET($S72,-COLUMNS($M72:O72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:O72),)," ")
P71	=IF(OR(OFFSET($S71,-COLUMNS($M71:P71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:P71),)," ")
P72	=IF(OR(OFFSET($S72,-COLUMNS($M72:P72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:P72),)," ")
Q71	=IF(OR(OFFSET($S71,-COLUMNS($M71:Q71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:Q71),)," ")
Q72	=IF(OR(OFFSET($S72,-COLUMNS($M72:Q72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:Q72),)," ")
R71	=IF(OR(OFFSET($S71,-COLUMNS($M71:R71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:R71),)," ")
R72	=IF(OR(OFFSET($S72,-COLUMNS($M72:R72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:R72),)," ")
S72	=IF(SUM(INDEX(SIGN((M72:R72="O")+(M72:R72="X")),0)),IF(G72-OFFSET(G72,-MATCH(1,INDEX(SIGN((M72:R72="O")+(M72:R72="X")),0),0),)>=I72,"X","O"))

 

[CThai]

Hi sanrv1f

I apologize for the confusing write up =(

what I'm tying to get is





IF G73 minus G72 is greater/equal to I73 then "X"
IF G72 minus G73 is greater/equal to I73 then "O"
IF G73 minus G71 is greater/equal to I73 then X
IF G71 minus G73 is greater/equal to I73 then "O"
IF G73 minus G70 is greater/equal to I73 then"X"
IF G70 minus G73 is greater/equal to I73 then "O"
If G73 minus G69 is greater/equal to I73 then "X"
If G69 minus G73 is greater/equal to I73 then "O"
IF G73 minus G68 is greater/equal to I73 then "X"


if it's NOT greater or equal to then "FALSE"

would it be OK if i send you an email or the workbook to show you want I'm tying to get?

again thank you for your help

 

[sanrv1f]

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S
71	23.91	2.200502	1.100251	1									O
72	24.17	2.200502	1.100251	2			O						FALSE
73	25.49	2.200502	1.100251	3				O					X
74	25.29	2.200502	1.100251	4			X		O				FALSE
75	25.48	2.200502	1.100251	5				X		O			FALSE
76	26.06	2.200502	1.100251	6					X		O		FALSE
77	25.92	2.200502	1.100251	7						X		O	FALSE
78	27.38	2.200502	1.100251	8							X		X
79	27.29	2.200502	1.100251	9			X					X	FALSE
80	27.41	2.200502	1.100251	10				X					FALSE
81	26.41	2.200502	1.100251	11					X				FALSE
82	24.69	2.200502	1.100251	12						X			O
83	25.53	2.200502	1.100251	13			O				X		FALSE
84	25.71	1.535899	0.76795	14				O				X	X
85	25.84	1.535899	0.76795	15			X		O				FALSE
86	26.86	1.535899	0.76795	16				X		O			X
Sheet2

Excel 2003

Cell Formulas
Range	Formula
I71	=H71/2
I72	=H72/2
J71	=IF(C71=C70,J70+1,1)
J72	=IF(C72=C71,J71+1,1)
K71	=IF(J72=1,J71," ")
K72	=IF(J73=1,J72," ")
L71	=IF(OR(OFFSET($S71,-COLUMNS($M71:M71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:M71),)," ")
M71	=IF(OR(OFFSET($S71,-COLUMNS($M71:M71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:M71),)," ")
M72	=IF(OR(OFFSET($S72,-COLUMNS($M72:M72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:M72),)," ")
N71	=IF(OR(OFFSET($S71,-COLUMNS($M71:N71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:N71),)," ")
N72	=IF(OR(OFFSET($S72,-COLUMNS($M72:N72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:N72),)," ")
O71	=IF(OR(OFFSET($S71,-COLUMNS($M71:O71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:O71),)," ")
O72	=IF(OR(OFFSET($S72,-COLUMNS($M72:O72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:O72),)," ")
P71	=IF(OR(OFFSET($S71,-COLUMNS($M71:P71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:P71),)," ")
P72	=IF(OR(OFFSET($S72,-COLUMNS($M72:P72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:P72),)," ")
Q71	=IF(OR(OFFSET($S71,-COLUMNS($M71:Q71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:Q71),)," ")
Q72	=IF(OR(OFFSET($S72,-COLUMNS($M72:Q72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:Q72),)," ")
R71	=IF(OR(OFFSET($S71,-COLUMNS($M71:R71),)={"X","O"}),OFFSET($S71,-COLUMNS($M71:R71),)," ")
R72	=IF(OR(OFFSET($S72,-COLUMNS($M72:R72),)={"X","O"}),OFFSET($S72,-COLUMNS($M72:R72),)," ")
S72	=IF(SUM(INDEX(SIGN((M72:R72="O")+(M72:R72="X")),0)),IF(G72-OFFSET(G72,-MATCH(1,INDEX(SIGN((M72:R72="O")+(M72:R72="X")),0),0),)>=I72,"X",IF(OFFSET(G72,-MATCH(1,INDEX(SIGN((M72:R72="O")+(M72:R72="X")),0),0),)-G72>I72,"O")))