sum(if( function where the argument logical_test contains arrays

Hi all,
I'd like to add a new condition to the formula below, which is supposed to calculate the average trade price in a few deals.

In English, this means that, if Selected Row's Date (B15) is less than or equal to today's date (B4), then do the following calculation. It's calculation trading results given (i) the security (cells HM$13), and (ii) the Settlement Date (cells $B15). I need to add the condition that Trade Date is different than the Settlement Date (both dates are in two different columns in another sheet, where each line displays one particular trade)

=IF($B15<=$B$4;IF(BG15<>0;SUM(IF((SettlDate_Blotter=$B15)*(Securities_Blotter=HM$13);Contracts_Blotter*TradePrice_Blotter))/BG15;SUM(IF((SettlDate_Blotter=$B15)*(Securities_Blotter=HM$13);Contracts_Blotter*TradePrice_Blotter)));0)

When Trade Date (shown as a column in sheet 2) = Settl. Date (shown as a different column in sheet 2), the calculation is exactly the one shown above. But when Trade Date is different than Settl. Date, the result should be brought to present value.


Any ideas??

Thanks in advance,
Bruno

Hi,
I can't use the AVERAGEIFS() function, because the Average price is the weighted average of the prices traded in that date, weighted by the volume of each trade (number of contracts, displayed in the array "Contracts_Blotter"

Originally Posted by daddylonglegs

I'm not sure what that means, what is "present value" in the context of the values shown here?

Try this formula to simplify your original

=IF($B15<=$B$4;SUMPRODUCT((SettlDate_Blotter=$B15)*(Securities_Blotter=HM$13);Contracts_Blotter;TradePrice_Blotter)/IF(BG15=0;1;BG15);0)

Hi,
Thanks for the simplified formula.
By present value I meant that, in cases where SettleDate_Blotter is different than TradeDate_Blotter, that is, when the security is traded today, but only settled tomorrow, this result should be multiplied by [1/(1+ daily interest rate)].
What I'm trying to figure out is a way of nesting this additional IF() function, which separates the lines in sheet 2 where the value in the vector TradeDate_Blotter is different than the one in the other vector, SettlDate_Blotter.
Of course the IF() function demanding that $B15 equals SettlDate_Blotter should remain active.

So, if $B15=SettlDate_Blotter, but SettlDate_Blotter is different than TradeDate_Blotter, the result should be multiplied by the discount factor. Otherwise, not.

Not sure if I'm making myself clear enough. It's pretty hard for a non-native speaker.

Many thanks in advance

Originally Posted by daddylonglegs

OK try this version

=IF($B15<=$B$4;SUMPRODUCT((SettlDate_Blotter=$B15)*(Securities_Blotter=HM$13);Contracts_Blotter;TradePrice_Blotter;1/(1+daily interest rate*(SettlDate_Blotter<>TradeDate_Blotter))/IF(BG15=0;1;BG15);0)

The problem with the formula above is that it results in a #DIV/0! error, probably because of this factor:

*(SettlDate_Blotter<>TradeDate_Blotter)

Any idea why that is? The array SettlDate_Blotter is a column in sheet two, which contains only dates. They're the settlement date of each trade, shown in a table in sheet two (each line is a trade done). The array TradeDate_Blotter is another column of the same table, which shows the date when the deal was done.

sry, nevermind the last reply. something was wrong with my formula