Arrayformulas with either/or nested to sumproduct if eitherconditions are true

J

Jonathan Pence

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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
 
C

Claus Busch

Hi Jonathan,

Am Thu, 27 Nov 2014 19:08:57 -0800 (PST) schrieb Jonathan Pence:
=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
try:
=IFERROR(SUMPRODUCT((($B$18:$B$35=AE14)+($B$18:$B$35=AE15))*$M$18:$M$35*$O$18:$O$35)/SUMPRODUCT((($B$18:$B$35=AE14)+($B$18:$B$35=AE15))*$M$18:$M$35),"")

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)
Click to expand...

add the argument in your formula similiar the other arguments


Regards
Claus B.
 
J

Jonathan Pence

I am in need of help in getting the OR, or similar, function nested in myarrayformula. 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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
Click to expand...

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$146)+($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$144)+($B$18:$B$35=$G$145)+($B$18:$B$35=$G$146)+($B$18:$B$35=$G$147))*$M$18:$M$35),"")
 
J

Jonathan Pence

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$146)+($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$144)+($B$18:$B$35=$G$145)+($B$18:$B$35=$G$146)+($B$18:$B$35=$G$147))*$M$18:$M$35),"")
Click to expand...

forgot Link (see table below data "the guts" (F141:AF169)

https://docs.google.com/spreadsheets/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
 
C

Claus Busch

Hi Jonathan,

Am Fri, 28 Nov 2014 04:38:48 -0800 (PST) schrieb Jonathan Pence:
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$146)+($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$144)+($B$18:$B$35=$G$145)+($B$18:$B$35=$G$146)+($B$18:$B$35=$G$147))*$M$18:$M$35),"")
Click to expand...

if you want add all values from G142:G147 you can also try
$B$18:$B$35<>"-"

Try:
=SUMPRODUCT(($A$18:$A$35=F142)*($B$18:$B$35<>"-")*$M$18:$M$35*$O$18:$O$35)/SUMPRODUCT(($A$18:$A$35=F142)*($B$18:$B$35<>"-")*$M$18:$M$35)


Regards
Claus B.
 
J

Jonathan Pence

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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
Click to expand...

Thanks,

Do I need to specify the range in the "-"? ex) $B$18:$B$35<>"$G$144:$G$147"
 
C

Claus Busch

Hi,

Am Fri, 28 Nov 2014 06:25:38 -0800 (PST) schrieb Jonathan Pence:
Do I need to specify the range in the "-"? ex) $B$18:$B$35<>"$G$144:$G$147"
Click to expand...

no, if you don't have any value from G144:G147 in column B you have a
hyphen into the cell. So you only have to calculate with the cells that
don't have a "-"to have all values


Regards
Claus B.
 
J

Jonathan Pence

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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
Click to expand...

thanks. that works, but when i want to split out the groupsets, like "executed w/itw + executed w/desk", and then "Proposed w/itw + proposed w/desk", how to I isolate them only? do I need to use a different funtion than "-" or just change it?

thanks,

Jon
 
C

Claus Busch

Hi Jonathan,

Am Fri, 28 Nov 2014 06:58:20 -0800 (PST) schrieb Jonathan Pence:
thanks. that works, but when i want to split out the groupsets, like "executed w/itw + executed w/desk", and then "Proposed w/itw + proposed w/desk", how to I isolate them only? do I need to use a different funtion than "-" or just change it?
Click to expand...

for G142 and G143 e.g. try:
=SUMPRODUCT(($A$18:$A$35=F142)*(($B$18:$B$35=G142)+($B$18:$B$35=G143))*$M$18:$M$35*$O$18:$O$35)/SUMPRODUCT(($A$18:$A$35=F142)*(($B$18:$B$35=142)+($B$18:$B$35=G142))*$M$18:$M$35)


Regards
Claus B.
 
J

Jonathan Pence

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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
Click to expand...

Claus, you rock. Sorry to ping you so much. But my last issue is the sum of the totals for each groupset.

I tried this:
arrayformula(SUM(IF(($A$18:$A$35="fixed - no swap")*($B$18:$B$35="proposed w/itw")+($B$18:$B$35="proposed w/desk"),$M$18:$M$35)))

but the total it gives is not right.

then, for the combined total tried this and worked:

arrayformula(sum(if(($A$18:$A$35=$F$142)*($B$18:$B$35<>"-"),$M$18:$M$35)))

what gives on the first?

Jon
 
C

Claus Busch

Hi Jonathan,

Am Fri, 28 Nov 2014 07:26:41 -0800 (PST) schrieb Jonathan Pence:
I tried this:
arrayformula(SUM(IF(($A$18:$A$35="fixed - no swap")*($B$18:$B$35="proposed w/itw")+($B$18:$B$35="proposed w/desk"),$M$18:$M$35)))
Click to expand...

try SUMPROSUCT instead:
=SUMPRODUCT(($A$18:$A$35="fixed - no swap")*(($B$18:$B$35="proposed w/itw")+($B$18:$B$35="proposed w/desk"))*$M$18:$M$35)
and make sure that the added part in column B is in brackets:
(($B$18:$B$35="proposed w/itw")+($B$18:$B$35="proposed w/desk"))
Each argument in brackets and the sum in brackets
then, for the combined total tried this and worked:

arrayformula(sum(if(($A$18:$A$35=$F$142)*($B$18:$B$35<>"-"),$M$18:$M$35)))
Click to expand...

You can also use a SUMPRODUCT to avoid the curled brackets:
=SUMPRODUCT(($A$18:$A$35=$F$142)*($B$18:$B$35<>"-")*$M$18:$M$35)
or
=SUMPRODUCT(--($A$18:$A$35=$F$142),--($B$18:$B$35<>"-"),$M$18:$M$35)



Regards
Claus B.
 
J

Jonathan Pence

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/d/1SEn1DO70CfS1KmRTY-kYSEA63oV5dOoWTjrDLPm664w/edit?usp=sharing
Click to expand...

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
 
C

Claus Busch

Hi Jonathan,

Am Fri, 28 Nov 2014 08:11:55 -0800 (PST) schrieb Jonathan Pence:
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)
Click to expand...

check the character in column B. I saw the hyphen. Evenutally there are
also one or more leading or trailing spaces in the cell. Then change the
hyphen with the cell content. Or run Data => TextToColumns for column B
to delete the spaces. The formula is correct.


Regards
Claus B.
 
C

Claus Busch

Hi again,

Am Fri, 28 Nov 2014 17:22:12 +0100 schrieb Claus Busch:
check the character in column B. I saw the hyphen. Evenutally there are
also one or more leading or trailing spaces in the cell. Then change the
hyphen with the cell content. Or run Data => TextToColumns for column B
to delete the spaces. The formula is correct.
Click to expand...

if you cannot get it to work then add all values for column B in the
formula:
=SUMPRODUCT(($A$18:$A$35=F142)*(($B$18:$B$35=G144)+($B$18:$B$35=G145)+($B$18:$B$35=G146)+($B$18:$B$35=G147))*$M$18:$M$35)


Regards
Claus B.
 
Top