Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 28th 14, 04:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old November 28th 14, 05:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2014
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  
Old November 28th 14, 05:22 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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  
Old November 28th 14, 05:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
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 12:37 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017