How to do a dynamic table using SUMPRODUCT?

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
Hi,

I'm trying to do a dynamic table that would change based on certain filters that I put. I would like to have a table that refers to a range in another worksheet in Excel and returns the top results given the parameters (filters) that I put in. So let's say, I would input filters that would request to pull bonds that have an maturity date between 2021 and 2025, have a BBB credit rating, have a spread above 100 above government bonds and the table would show the corresponding top results.

Here is an example of the filters:
Security selection dashboard - 2020-04-30.xlsx
QR
1CriteriaParameters
2Min Mat YR2021
3Max Mat YR2025
4RatingBBB
5Spread >100
6Sector 1CORP
Dashboard


Here is an example of the table that would be outputting the results and the formula I'm trying right now (but that isn't working):
Security selection dashboard - 2020-04-30.xlsx
ABCDEFGHIJKLMN
1ID_CUSIPNameTickerCoupon RateMaturity DateSector 1Sector 2RatingBB_5Y_DEFAULT_PROBAsk PriceAsk SpreadBase CurrencyCOUNTRYSpread/5 YR default rate
2              
3              
4              
5              
Dashboard
Cell Formulas
RangeFormula
A2:N5A2=IFERROR(INDEX(Rankings!$A$1:$Z$10000,SMALL(IF((YEAR(Rankings!$J$1:$J$10000)>=$Q$2)*(YEAR(Rankings!$J$1:$J$10000)<=$Q$3)*(Rankings!$K$1:$K$10000=$Q$4)*(Rankings!$D$1:$D$10000=$Q$5)*(Rankings!$E$1:$E$10000=$Q$6),ROW(Rankings!$O$1:$O$10000)),ROW(1:1)),MATCH(A$1,Rankings!$A$1:$Z$1,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


Here is the range in another sheet that I would refer to:
Cell Formulas
RangeFormula
A2:A7,E2:N7A2=IFERROR(INDEX('C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$A$2:$AP$99998,MATCH(LEFT($B2,LEN($B2)-5),'C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$B$2:$B$14998,0),MATCH(A$1,'C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$2:$2,0)),"")
B2B2=IF('RUNZ Export'!A1219<>0,'RUNZ Export'!A1219,"")
C2:D7C2=IFERROR(INDEX('RUNZ Export'!$A:$X,MATCH(INDIRECT("B"&ROW()),'RUNZ Export'!$A:$A,0),MATCH(Rankings!C$1,'RUNZ Export'!$1:$1,0)),"")
O2:O7O2=IFERROR(IFERROR(((D2^3)/100000000)/L2,((D2^3)/1000000)/INDEX(Table1[#All],MATCH(INDIRECT("K"&ROW()),Table1[[#All],[CORP Equivalent Rating]],0),MATCH("Default Risk %",Table1[#Headers],0))),"")
B3B3=IF('RUNZ Export'!A383<>0,'RUNZ Export'!A383,"")
B4B4=IF('RUNZ Export'!A382<>0,'RUNZ Export'!A382,"")
B5B5=IF('RUNZ Export'!A381<>0,'RUNZ Export'!A381,"")
B6B6=IF('RUNZ Export'!A380<>0,'RUNZ Export'!A380,"")
B7B7=IF('RUNZ Export'!A379<>0,'RUNZ Export'!A379,"")


I think the problem I'm having is related to the fact that the above range I'm referring to is a filtered range with TONS of blanks and errors in it. I would probably need to find a way to filter out the errors and the blanks and return the clean results in order for my formula to work. Here is the formula I was playing around with for now: =IFERROR(INDEX(Rankings!$A$1:$Z$10000,SMALL(IF((YEAR(Rankings!$J$1:$J$10000)>=$Q$2)*(YEAR(Rankings!$J$1:$J$10000)<=$Q$3)*(Rankings!$K$1:$K$10000=$Q$4)*(Rankings!$D$1:$D$10000=$Q$5)*(Rankings!$E$1:$E$10000=$Q$6),ROW(Rankings!$O$1:$O$10000)),ROW(1:1)),MATCH(B$1,Rankings!$A$1:$Z$1,0)),"")

Any help would be extremely appreciated to help me figure out why it isn't working and/or if I am right and it is linked to a filtered range with blanks & errors; how would you amend my formula to ignore those blanks and return the top results for my search?

Thank you in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,584
Office Version
  1. 365
Platform
  1. Windows
Main problem that I can see is that your formula is referring to the criteria description ($Q$2 etc) where it should be referring to the parameter ($R$2 etc).

It might need other changes, for the filter, blanks, errors, etc. Instead of using
SMALL(IF((range1 = criteria1)*(range2 = criteria2),ROW(range)),ROW(1:1))
try
AGGREGATE(15,6,ROW(range)/(range1=criteria1)/(range2=criteria2),ROWS(A$2:A2))

Note that ROW(range) is moved to the front of the array. There is no IF() function. Division is used instead of multiplication. You don't need to array confirm this method.
Finally, the last part uses ROWS(A$2:A2) instead of ROW(1:1) with the range referring to the top left cell in the range holding the formulas with the single $ symbol to make it dynamic.

That should help with any formula errors in the data. You might need additional changes to work with filtered data or blanks not excluded by the criteria in R2:R6
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
Main problem that I can see is that your formula is referring to the criteria description ($Q$2 etc) where it should be referring to the parameter ($R$2 etc).

It might need other changes, for the filter, blanks, errors, etc. Instead of using
SMALL(IF((range1 = criteria1)*(range2 = criteria2),ROW(range)),ROW(1:1))
try
AGGREGATE(15,6,ROW(range)/(range1=criteria1)/(range2=criteria2),ROWS(A$2:A2))

Note that ROW(range) is moved to the front of the array. There is no IF() function. Division is used instead of multiplication. You don't need to array confirm this method.
Finally, the last part uses ROWS(A$2:A2) instead of ROW(1:1) with the range referring to the top left cell in the range holding the formulas with the single $ symbol to make it dynamic.

That should help with any formula errors in the data. You might need additional changes to work with filtered data or blanks not excluded by the criteria in R2:R6

The Q2 vs R2 reference was a non-issue because I deleted a column after posting the parameters table above. But it was referencing to the proper cell.
However, your suggestion of using aggregate (which is a function im a bit less familiar with) rather than SMALL seems to have settled the issue that I was experiencing with the blanks and errors. I'm not sure I understand the difference yet to be honest even though I would love to. Either way, thank you so much for your suggestion! If you have time to perhaps explain to me why aggregate works and why small did not, it would be great.

One way or another, your help was extremely appreciated. Thank you again!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,584
Office Version
  1. 365
Platform
  1. Windows
Aggregate is able to ignore errors while small would need additional functions to filter them out.

In addition to this the division method creates errors for the rows that don't meet the criteria (meaning that they are ignored by aggregate) where the multiplication of small(if creates zero values for the same rows. Being smaller than the valid row numbers, small passes the zero's to index first, which means that it is returning the entire column instead of a single row. Because your formula is only meant to return a single cell result index returns an error which iferror is then blanking out.
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117

ADVERTISEMENT

While you understand the issue, can I you 2 quick follow up questions?
1- In the ratings parameter: there are essentially 3 options; one rating with a +, with no sign, and with a - (e.g. BBB+, BBB, BBB-, or AA+, AA, AA-, or A+,A,A-). How would I go about grouping all ratings of one type (BBB, AA, A) regardless of the sign into one group so that when I put BBB in R4 in the parameters table above, it returns bonds of all 3 subcategories BBB+, BBB, and BBB- instead of just BBB.

2- How do I enter a wildcard in the parameters table in order to indicate that anything can be returned in this field. Basically, without changing the formulas in the output table, I could put in the equivalent of "*" in R4 or R5 and then it would return any credit rating with any spread without filtering for those parameters because I entered a wildcard for the parameters in question?

Thanks again!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,584
Office Version
  1. 365
Platform
  1. Windows
The first part is going to make the formula excessively complicated, using wildcards would allow false results (A with a wildcard would return AA as well). The only reliable way would be to use

/(((Range1=Criteria1)+(Range1=(Criteria1&"-"))+(Range1=(Criteria1&"+")))>0)/

For the rating criteria section of the array.

The formula is not suitable for wildcards in the criteria cells, they would have to go into the formula, again increasing the complexity of the array. Previously I've advised people to use a criteria of "All" for this purpose, with an array of

/IF(Criteria="All",1,Range1=Criteria1)/

Adding IF into the formula means that it will also need to be array confirmed. The TRUE result of 1 when criteria ="All" means division by 1, which is no change to the array so all rows are accepted, nothing is filtered out.
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
The first part is going to make the formula excessively complicated, using wildcards would allow false results (A with a wildcard would return AA as well). The only reliable way would be to use

/(((Range1=Criteria1)+(Range1=(Criteria1&"-"))+(Range1=(Criteria1&"+")))>0)/

For the rating criteria section of the array.

The formula is not suitable for wildcards in the criteria cells, they would have to go into the formula, again increasing the complexity of the array. Previously I've advised people to use a criteria of "All" for this purpose, with an array of

/IF(Criteria="All",1,Range1=Criteria1)/

Adding IF into the formula means that it will also need to be array confirmed. The TRUE result of 1 when criteria ="All" means division by 1, which is no change to the array so all rows are accepted, nothing is filtered out.

This works just perfectly. Thank you SO much! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,146
Members
412,574
Latest member
shadowfighter666
Top