| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with Matrices
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
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 "Stacey" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
Thank you so much. I should have thought of that. Thanks again.
"Toppers" wrote: 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 "Stacey" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
See BJ's reply re "intermediate" values.
"Stacey" wrote: Thank you so much. I should have thought of that. Thanks again. "Toppers" wrote: 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 "Stacey" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
Thank you for helping. I was able to get Topper's to work. I appreciate
your time. "bj" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct with Matrices
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. "Toppers" wrote: See BJ's reply re "intermediate" values. "Stacey" wrote: Thank you so much. I should have thought of that. Thanks again. "Toppers" wrote: 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 "Stacey" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i square matrices? | Excel Worksheet Functions | |||
| Comparing two matrices | Excel Discussion (Misc queries) | |||
| inverse matrices | Excel Discussion (Misc queries) | |||
| matrices | Excel Discussion (Misc queries) | |||
| How to solve y =f(x,y) using matrices? | Excel Worksheet Functions | |||
Linear Mode
