conditional & comparative Formula

[metropical]

I've asked a version of this earlier, but my reasoning was wrong.

for lack of a better description....

Here's the data:

Disregard the XTY column.
Otherwise, columns A, B, & C are shown for calcs in D & E not shown.

In order to calculate fees.
IF (odd/even pairs) A1:2 both start with the same 3 or 4 letters (symbol)
AND B1:2 are both ("* Close")
THEN E2 = SUM C1:2/100* 1.50
THEN D2 = 14
OR
IF (odd/even pairs) A1:2 both start with the same 3 or 4 letters (symbol)
AND either B1 or B2 (but not both) is ("* Close")
THEN E2 = SUM C1:2/100* .75
THEN D2 = 7
By "odd even pairs" I mean comparing 2 rows at a time starting with an odd row each time.

Is there a way to write this formula, or is this beyond the scope of Excel?
I'm using an older version (X for Max, aka 2001), if that matters.

Thanks. Hope this is understandable.

 

[metropical]

Oh, I'm making this much to complex.

All I need is:
IF C3 contains "Close" or "Short" then Q3 is #

 

[AnAnalyst]

Hi, based on your original post:

D2 formula:

=IF(AND(LEFT(A1,3)=LEFT(A2,3),RIGHT(B1,5)="Close",RIGHT(B2,5)="Close"),14,IF(AND(RIGHT(B1,5)<>RIGHT(B2,5),OR(RIGHT(B1,5)="Close",RIGHT(B2,5)="Close")),7,""))

E2 formula:

=IF(AND(LEFT(A1,3)=LEFT(A2,3),RIGHT(B1,5)="Close",RIGHT(B2,5)="Close"),SUM(C1:C2)/100*1.5,IF(AND(RIGHT(B1,5)<>RIGHT(B2,5),OR(RIGHT(B1,5)="Close",RIGHT(B2,5)="Close")),SUM(C1:C2)/100*0.75,""))

... but from your last post that isn't what you want?

 

[metropical]

yes. I appreciate your work, but there are other variables that mess me up ......... I think.

I figured the "E" formula more simply as:
D9/100*0.75
and that works fine.

But my 2nd post is the one I can't figure.

something like ......

=IF(OR(C12="* Close",C12="* Short"),7)

 

[PaddyD]

"IF C3 contains "Close" or "Short" then Q3 is # "

=if(or(isnumber(search("close",c3)),isnumber(search("short",c3))),7,"neither close nor short")<!-- / message -->

 

[metropical]

sorry. I don't understand.

If C12 is either "* Close" or "* Short" then Q12 = #.

I tried:

IF(OR(C12="* Close",C12="* Short"),7,0)

 

[PaddyD]

"...I tried..."

You've re-tried your attempt, which we know doesn't work. Why don't you try mine & see how you get on.

 

[metropical]

IF(OR(RIGHT(C3,5)="Close",RIGHT(C3,5)="Short"),7,"")

was what worked, not that yours wouldn't, but I didn't understand how to effect the syntax.
Formula syntax isn't my strong suit.

Great sig PaddyD