On Thursday, November 27, 2014 9:08:59 PM UTC-6, Jonathan Pence wrote:
I am in need of help in getting the OR, or similar, function nested in my arrayformula. I was able to get the data I wanted for the first and second series using the following:

=iferror(arrayformula(sumproduct((O$18:O35*($B$18: $B$35=$AE15))*($M$18:$M35*($B$18:$B$35=$AE15)))/sum(($M$18:$M$35*($B$18:$B$35=$AE$15)))),"")

AE15=executed
AE14=consider

I would like to now get the combined sumproduct if either is true.

how do I nest that function into my current or different formula

Then, the data series B18:B35 has drop down options (executed, Consider), and A18:A35 also has drop down options (Fixed, Floating to the Call, Floating to Maturity, Zero Coupon).

could I use the same formula to satisfy the following: if A18:A35=Floating to Maturity +(or) Floating to the call AND executed +(or) consider, Sumproduct(N18:N35,M18:M35)/sum(M18:M35)

Thanks,

Jon


here is the link in case you want to look around:

https://docs.google.com/spreadsheets...it?usp=sharing

everything works except the "combined" formula:

=SUMPRODUCT(($A$18:$A$35=$F$142)*($B$18:$B$35<"-")*$M$18:$M$35*N$18:N$35)/SUMPRODUCT(($A$18:$A$35=$F$142)*($B$18:$B$35<"-")*$M$18:$M$35)

check:
Fixed Executed & Proposed
Par book ytc ytc tey
500,000 522,500 2.00 3.02
500,000 532,500 3.00 4.56
500,000 537,500 4.00 6.10
500,000 542,500 3.00 2.25
2,000,000.00 2,135,000.00 3.007 3.986

formula returns: 2.52 (not 3.007), and 3.82 (not 3.986)

have I entered your formula wrong?

Jon