Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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)


add the argument in your formula similiar the other arguments


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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),"")
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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$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),"")


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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,

Do I need to specify the range in the "-"? ex) $B$18:$B$35<"$G$144:$G$147"
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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"


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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, 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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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?


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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


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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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)))


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)))


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Arrayformulas with either/or nested to sumproduct if eitherconditions are true

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
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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)


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Arrayformulas with either/or nested to sumproduct if either conditions are true

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.


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$3 5=G147))*$M$18:$M$35)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
if true, if not nested if funcion Cali Excel Worksheet Functions 2 May 24th 10 07:15 PM
IF statement with nested SUMPRODUCT? Bazael Excel Discussion (Misc queries) 3 June 25th 08 09:52 PM
Sumproduct with nested sum sahafi Excel Worksheet Functions 6 March 19th 07 05:29 PM
If condition is true return sumproduct of two arrays Felipe Excel Worksheet Functions 0 October 14th 05 05:26 AM
If condition is true return sumproduct of two arrays Duke Carey Excel Worksheet Functions 0 October 14th 05 05:24 AM


All times are GMT +1. The time now is 02:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"