Sumproduct with Matrices

S

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
 
T

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
 
B

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top