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

# Excel IF Help

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

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

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

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

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

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

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

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

• ###### Excel IF Help

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

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

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

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

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

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