Remember Me?

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

=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
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
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:

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Cali Excel Worksheet Functions 2 May 24th 10 07:15 PM Bazael Excel Discussion (Misc queries) 3 June 25th 08 09:52 PM sahafi Excel Worksheet Functions 6 March 19th 07 05:29 PM Felipe Excel Worksheet Functions 0 October 14th 05 05:26 AM Duke Carey Excel Worksheet Functions 0 October 14th 05 05:24 AM

All times are GMT +1. The time now is 12:37 AM.