IF statement

C

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
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!!

Code: 
   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
Code: 
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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.
 
Upvote 0
Hi Misca

do you know where i can go and find examples? I'm not really good at vba ( beginners)
 
Upvote 0
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?
 
Upvote 0
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

Code: 
=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")),"  "))))))
 
Upvote 0
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




Code: 
=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
 
Upvote 0
still I dont understand what you are trying, any how, see if the below comes anywhere closer to what you need
Excel Workbook
GHIJKLMNOPQRS
7123.912.2005021.1002511O
7224.172.2005021.1002512OO
7325.492.2005021.1002513OOX
7425.292.2005021.1002514XOOO
7525.482.2005021.1002515OXOOO
7626.062.2005021.1002516OOXOOO
7725.922.2005021.1002517OOOXOOO
7827.382.2005021.1002518OOOOXOX
7927.292.2005021.1002519XOOOOXO
8027.412.2005021.10025110OXOOOOO
8126.412.2005021.10025111OOXOOOO
8224.692.2005021.10025112OOOXOOO
8325.532.2005021.10025113OOOOXOO
8425.711.5358990.7679514OOOOOXO
8525.841.5358990.7679515OOOOOOO
8626.861.5358990.7679516OOOOOOX
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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"))
 
Upvote 0
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
 
Upvote 0
Excel Workbook
GHIJKLMNOPQRS
7123.912.2005021.1002511O
7224.172.2005021.1002512OFALSE
7325.492.2005021.1002513OX
7425.292.2005021.1002514XOFALSE
7525.482.2005021.1002515XOFALSE
7626.062.2005021.1002516XOFALSE
7725.922.2005021.1002517XOFALSE
7827.382.2005021.1002518XX
7927.292.2005021.1002519XXFALSE
8027.412.2005021.10025110XFALSE
8126.412.2005021.10025111XFALSE
8224.692.2005021.10025112XO
8325.532.2005021.10025113OXFALSE
8425.711.5358990.7679514OXX
8525.841.5358990.7679515XOFALSE
8626.861.5358990.7679516XOX
Sheet2
Excel 2003
Cell Formulas
RangeFormula
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")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Circular reference?
Replies
6
Views
334
RoryA
RoryA
J
  • Question Question
I could use Help with a loop that I am doing Manually. I struggle with the loops.
Replies
2
Views
493
DanteAmor
DanteAmor
T
  • Solved
Index match with multiple criteria, sheets & columns
2
Replies
10
Views
734
Fluff
Fluff
M
  • Question Question
VLOOKUP?
Replies
1
Views
213
MeaclH
M
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
85
Fluff
Fluff

Forum statistics

Threads
1,223,922
Messages
6,175,386
Members
452,639
Latest member
RMH2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top