Sumproduct with Matrices

[Stacey]

I have the following matrix: The numbers in the column to the far left are
the range for the first growth and the numbers on the last row are the ranges
for the second growth. The percentages in the middle represent the discount
rate that the customer gets for the combination of growths. I am using the
following formula:

=SUMPRODUCT((Matrix!$B$2:$B$7=Summary!H12)*(Matrix!$C$8:$H$8=Summary!J12)*(Matrix!$C$2:$H$7))

This formula works great until one of the growth rates =0. It doesn't seem
to be able to pick up the percentage payout. For example, of Growth rate #1
= 0% and growth rate #2 = 10%, the customer should get 1.5% discount. Can
anyone help me pleaes?

Growth #1
9999.90% 2.25% 4.00% 4.50% 5.00% 5.50% 8.00%
25% 2.00% 3.50% 4.00% 4.50% 5.00% 7.00%
20% 1.75% 3.00% 3.50% 4.00% 4.50% 6.00%
15% 1.50% 2.50% 3.00% 3.50% 4.00% 5.00%
10% 1.25% 2.00% 2.50% 3.00% 3.50% 4.50%
5% 1.00% 1.50% 2.00% 2.50% 3.00% 4.00%
0% 5% 10% 15% 20% 25% 10000%
Growth #2

 

[Toppers]

If your data starts in row 2, then B2:B7 excludes the 0%. Equally if Growth 2
can be 0% this not include in C8H8.

I think you need another column to cater for 0%

=SUMPRODUCT((Matrix!$B$2:$B$8=Summary!H12)*(Matrix!$C$9:$I$9=Summary!J12)*(Matrix!$C$2:$I$8))

9999.90% ? 2.25% 4.00% 4.50% 5.00% 5.50% 8.00%
25% ? 2.00% 3.50% 4.00% 4.50% 5.00% 7.00%
20% ? 1.75% 3.00% 3.50% 4.00% 4.50% 6.00%
15% ? 1.50% 2.50% 3.00% 3.50% 4.00% 5.00%
10% ? 1.25% 2.00% 2.50% 3.00% 3.50% 4.50%
5% ? 1.00% 1.50% 2.00% 2.50% 3.00% 4.00%
0% ? ? ? ? ? ? ?
0.00% 5% 10% 15% 20% 25% 10000%

HTH

 

[bj]

are you sure you are getting the right values for the other areas
what happens when you enter 4% and 12 %
you don't have a line for 0 % in your matrix
if the column b and row 8 is supposed to represent "up to and including"
I would recommend inverting the table such that the value matrix goes in c3
as 1% to H8 as 5.5 %
set C2:H2 as 0,5,10,15,20,25 %
Set B3:B8 as 0,5,10,15,20,25 %

now =vlookup(Summary!H12,B3:H8,match(Summary!U12,B2:H2,0),0)

 

[Stacey]

Thank you so much. I should have thought of that. Thanks again.

 

[Toppers]

See BJ's reply re "intermediate" values.

Thank you so much. I should have thought of that. Thanks again.

 

[Stacey]

Thank you for helping. I was able to get Topper's to work. I appreciate
your time.

 

[Stacey]

I have inserted columns that take the actual growth rates and rounds them up
to the nearest matrix value, this way the intermediate values are counted.
Thank you.