Posted to microsoft.public.excel.worksheet.functions
|
|
Arrayformulas with either/or nested to sumproduct if eitherconditions are true
On Friday, November 28, 2014 6:38:52 AM UTC-6, Jonathan Pence wrote:
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
Thanks. That works. Not getting the added function to work yet:
A18:A35 search field, find F142:F147 (in this case F142=fixed no swap)
Fixed no Swap
Need Level to call
Need Level to Maturity
Live Level to Call
Live Level to Maturity
B18:B35 search field, find G142:G147 (in this case G144=executed w/itw + G145=executed w/desk + G146=Proposed w/itw + G147=proposed w/desk)
Executed w/itw
Executed w/desk
Proposed w/itw
Proposed w/itw
Tried:
IFERROR(SUMPRODUCT((($A$18:$A$35=$F$142)*($B$18:$B $35=$G$144)+($B$18:$B$35=$G$145)+($B$18:$B$35=$G$1 46)+($B$18:$B$35=$G$147))*$M$18:$M$35*O$18:O$35)/SUMPRODUCT((($A$18:$A$35=$F$142)*($B$18:$B$35=$G$1 44)+($B$18:$B$35=$G$145)+($B$18:$B$35=$G$146)+($B$ 18:$B$35=$G$147))*$M$18:$M$35),"")
forgot Link (see table below data "the guts" (F141:AF169)
https://docs.google.com/spreadsheets...it?usp=sharing
|