Matrix Problem
Posted by Michael on November 20, 2001 11:13 AM
Below is info to calculate how many total customers you have after 4 months - the catch being that new markets sign on each month. All markets signing on have the same growth curve (8 customers in the first month, then 10, 20 and 30 in the 4th month). Out of this data matrix, how do you calculate how many total customers you have in the 4th month?
Posted by Aladin Akyurek on December 02, 2001 4:52 AM
Michael has already in his possession the 'non-matrix' solution that follows (reproduced just for the record).
Lets say that A3:E6 houses the sample data:
{"month",1,2,3,4;"new mkts",1,3,2,4;"growth",8,10,20,30;"total mkts",1,4,6,10}
In A1 enter: =MATCH(9.99999999999999E+307,3:3)
In A2 enter: ="$B$5:"&ADDRESS(5,A1)
In A8 enter: Total Customers
In B8 enter: =IF(ISNUMBER(B9),SUM(B9:INDIRECT(ADDRESS(ROW()+B$3,COLUMN(B9)))),"") [ copy this across till column E ]
In B9 enter: =IF(B$4,IF(COLUMN()<=$A$1,$B4*(INDEX(INDIRECT($A$2),COUNT($B4:B4))),""),"") [ copy this across till column E ]
In C10 enter: =IF($C4,IF(COLUMN()<=$A$1,$C4*(INDEX(INDIRECT($A$2),COUNT($C4:C4))),""),"") [ copy this across till column E ]
In D11 enter: =IF($D4,IF(COLUMN()<=$A$1,$D4*(INDEX(INDIRECT($A$2),COUNT($D4:D4))),""),"") [ copy this across till column E ]
In E12 enter: =IF($E4,IF(COLUMN()<=$A$1,$E4*(INDEX(INDIRECT($A$2),COUNT($E4:E4))),""),"")
Aladin
========
