13 Replies Latest reply: May 15, 2014 10:08 AM by Olly Bond RSS

    Excel IF Help

    adonis _

      Hi All, hope all are doing well, long time no speak.

       

       

      I have a question as follows:

       

      I am trying to do a if statement that looks into 4 conditions, it would have been easier if the description field only had the word put or call in it but since it starts the line i thought i used the following formula it would work

       

      =IF(D2="CALL"&C2>=0,C2100,IF(D2="CALL"&C2<0,C2-100,IF(D2="PUT"&C2>=0,C2-100,IF(D2="PUT"&C2<0,C2100))))

       

       

      D2 = Description field

      C2 = quantity field

       

      4 possible data scenarios as follows

      Qty         Description

      367     CALL ACL    09/18/10   160

      -1500     CALL AUY    09/18/10     9

      78     PUT  SD     09/18/10    11

      -6110     PUT SPX    09/18/10  1050

       

      problem is that it is not working properly

      any help would be appreciated.

        • Excel IF Help
          Data Kruncher

          Good one today Adonis, if completely off topic.

           

          Anyway, you're trying to use the & operator as a logical AND operator (Monarch's .And.), but that not what it does. It concatenates text, like Monarch's + operator. You need Excel's AND() function instead.

           

          Further, Excel's IF() function doesn't support wildcards. You need to kind of build the functionality that you want from scratch. The SEARCH function returns a value of 1 if a match to a wildcard is found, but a #VALUE error if it's not. So use that, and then check to see if it returned a number with ISNUMBER().

           

          Put it all together, and you get the rather lengthy yet (I suspect) functional:

          =IF(AND(ISNUMBER(SEARCH("CALL",D2)),C2>=0),C2100,

          IF(AND(ISNUMBER(SEARCH("CALL",D2)),C2<0),C2-100,

          IF(AND(ISNUMBER(SEARCH("PUT",D2)),C2>=0),C2-100,

          IF(AND(ISNUMBER(SEARCH("PUT",D2)),C2<0),C2100,0))))[/CODE]

           

          Does that work for you?

            • Excel IF Help
              adonis _

              Hi Data

              it is not working

              still shows up whacky

              same thing, is there any way to do in excel if statement an in "Call*" type of lookup because the reason is in theory i would either need to get left 4 for the field = call or left 3 = put if available, since those are 2 conditions.

                • Excel IF Help
                  Data Kruncher

                  Yes, you can go that route, and in fact I almost did.

                   

                  But first, what are the correct answers that you're expecting for these four test records?

                    • Excel IF Help
                      adonis _

                      Report Date     Qty                Description     Shares

                      9/17/2010     367     CALL ACL    09/18/10   160     36700

                      9/17/2010     -1000     CALL XOM    09/18/10    60     -100000

                      9/17/2010     -1500     PUT  AA     09/18/10    13     150000

                      9/17/2010     10100     PUT  C      09/18/10     4     -1010000

                       

                      p.s. i multiply the QTY data by 100 because it is a contract of 100 as you noticed

                       

                      If QTY contains positive and Description contains call, shares = positive                         

                      If QTY contains negative and Description contains call,shares = negative                         

                      If QTY contains positive and Description contains put,shares = negative                         

                      If QTY contains negative and Description contains put,shares = positive

                        • Excel IF Help
                          Data Kruncher

                          OK, then this is the formula:

                           

                          =IF(AND(ISNUMBER(SEARCH("CALL",D2)),C2>=0),C2100,

                          IF(AND(ISNUMBER(SEARCH("CALL",D2)),C2<0),C2100,

                          IF(AND(ISNUMBER(SEARCH("PUT",D2)),C2>=0),C2-100,

                          IF(AND(ISNUMBER(SEARCH("PUT",D2)),C2<0),C2-100,0))))[/code]

                            • Excel IF Help
                              adonis _

                              you are a genius

                              thanks my brother

                              if you are ever in NYC area give me a email or something so i can buy a drink

                              i guess i owe a few guys a few beers

                                • Excel IF Help
                                  Data Kruncher

                                  Reserve the VIP section and start running a tab. ;):D

                                   

                                  Glad to help, of course.

                                    • Excel IF Help
                                      adonis _

                                      ha, be safe bud, thanks again, if you should come, not sure about a VIP section have 3 kids to feed, but definitely will find a good meal and drinks

                                        • Excel IF Help
                                          Olly Bond

                                          Hello Adonis,

                                           

                                          Forgive me for springing into action at the mention of "beers", but actually meeting other users and learning from each other's experiences, as well as enjoying a beer or two, sounds like a great idea.

                                           

                                          I'm thinking about organising a European user conference next year - if any other Monarch users over here are interested please PM me or email me - but with quite a few users in Eastern US perhaps New York would be a sensible idea as well.

                                           

                                          The Datawatch conferences ran for three days, and included a huge range of training options. I'm pretty sure we could get a decent programme into two days, and deliver an interesting set of presentations, some discussion panels, some expert help, some training at various levels, and crucially, a real overview of the options for report mining on the desktop, the server and the browser.

                                           

                                          It would take a little time to put together, but how about we aim for a two day event in New York in May 2011? Kruncher, would you be willing to help us put it together?

                                           

                                          Best wishes,

                                           

                                          Olly

                                            • Excel IF Help
                                              adonis _

                                              sounds good Olly

                                              have a few beers and share some good stories.

                                              God Willing we are employed and such. 

                                              Since I am across the Pond in NYC area, most likely require bringing the family where they can go out and have fun and such while some of us can juggle both tasks.

                                              put me down for it, always willing to meet nice people, perhaps make some of my Dutch Friends come visit me there as well.

                                               

                                              I am sure others would be interested as well,

                                               

                                              could be scheduled somewhere where we have it after labor day where we officially start summer?

                                               

                                              and hopfully we are employed at the time, as that would suck to commit to something like that when no resources available. but for the time being sounds like you can count me in for now.

                                                • Excel IF Help
                                                  Data Kruncher

                                                  Adonis, I think that you read Olly's post a bit too quickly. He mentioned possibly running an NYC area get-together in May 2011. Your back yard. No travel required - for you!

                                                   

                                                  Sounds like an interesting idea, which merits being discussed further. Care to start a separate thread Mr. Bond?

                                                • Excel IF Help
                                                  adonis _

                                                  getting old my friend glasses must have bipassed seeing it,

                                                  okay, if in NYC definetely down, but if in Europe, might want to attend that as well if resources and the stars are aligned properly

                                                   

                                                   

                                                  talk to you soon my friend, have a great day

                                                  didn't realize i had not logged on since end of January, wow.

                                                    • Excel IF Help
                                                      Olly Bond

                                                      Alright then - I'll start a thread in the 2008(!) conference section, so it's not in the way of users' posts here in the main forum. Anyone who's interested, even vaguely, in somehow being involved (speaking, helping, attending, whatever) for two days in New York next May, please post there or contact me and I'll keep everyone informed...

                                                       

                                                      Best wishes,

                                                       

                                                      Olly