PDA

View Full Version : A complex IF formula



Riaaz66
06-23-2008, 05:35 AM
Hi,

I have to calculate some fees with a complex condition. Here it comes:
(see example sheet attached)

Every SELL is EUR 0.20
Every BUY is EUR 0.20 BUT only the first Buy trade per day per symbol has to pay 0.50 more (so a total of EUR 0.70)

Can somebody help me with this?

Thanks in advance and kind regards,

Riaaz66

Bob Phillips
06-23-2008, 05:47 AM
=IF(B2="S",0.2,0.2+(SUMPRODUCT(--($A$2:A2=A2),--($D$2:D2=D2))=1)*0.5)

Bob Phillips
06-23-2008, 05:49 AM
Without an IF

=0.2+(SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2="B"),--($D$2:D2=D2))=1)*0.5

Riaaz66
06-23-2008, 07:34 AM
=IF(B2="S",0.2,0.2+(SUMPRODUCT(--($A$2:A2=A2),--($D$2:D2=D2))=1)*0.5)
Hi Xld,

I tried both formulas. the last one doesn't work. The first one works partly.

Bob Phillips
06-23-2008, 08:09 AM
More detail because it worked absolutely as you predicted for me on your example workbook.

davidw
11-08-2008, 08:52 PM
you guys have lots of great info in here.