Sumproduct or better quicker formula

[dariushou]

Hello there,

I'm trying to figure out the best (the quickest for calculations) formula to use for my data set. My data is laid out in the following way:

I have about 30 sections in one worksheet. Each section has 6 scenarios. For example, the first section in the below example is "Average Balance Factor" and it has 6 scenarios accross columns B thru G. The data goes down about 600 rows--each row represents a month so "1" would equal month 1 and so on.

I have a model in another worksheet within the same workbook that looksup the relevant period and scenario that i'm using and uses that data in a calculation. I have a macro that goes through a loop of different loans and each loan could use a different scenario (depending on what scenario i've given to such loan).

I know that i can use the following two formulas (for the "Average Balance Factor as an example")

SUMPRODUCT((Data!$B$1:$G$1=Model!$B$13)*(Data!$A$6:$A$15=Model!$A26),Data!$B$6:$G$15)

With B13 being the scenario for that loan (B13 is populated by the macro)
A26 is just the current period

CSE formula array works as well
SUM(IF((Data!$B$1:$G$1=Model!$B$13)*(Data!$A$6:$A$15=Model!$A26),Data!$B$6:$G$15))

Because the data goes out 600 rows and is now over 180 columns (30 sections with 6 scenarios each) is there a better way than the sumproduct approach. I'm looking for speed here. Thanks,

For your help.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Scenario	1	2	3	4	5	6	1	2	3	4	5	6	1	2	3	4	5	6
2
3
4	Period	Average Balance Factor						Penetration Percentage						Premium
5
6	1	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
7	2	99.98%	99.98%	99.98%	99.98%	99.98%	99.98%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
8	3	99.60%	99.60%	99.60%	99.60%	99.60%	99.60%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
9	4	99.55%	99.55%	99.55%	99.55%	99.55%	99.55%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
10	5	99.35%	99.35%	99.35%	99.35%	99.35%	99.35%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
11	6	99.03%	99.03%	99.03%	99.03%	99.03%	99.03%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
12	7	98.83%	98.83%	98.83%	98.83%	98.83%	98.83%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
13	8	98.50%	98.50%	98.50%	98.50%	98.50%	98.50%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
14	9	97.89%	97.89%	97.89%	97.89%	97.89%	97.89%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
15	10	97.43%	97.43%	97.43%	97.43%	97.43%	97.43%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
Data

 

[Fazza]

FYI, at Charles Williams' great site http://www.decisionmodels.com/optspeede.htm

 

[shemayisroel]

Great. Thanks you two. That was the issue. I need to learn the index/match.

Try also

http://www.contextures.com/xlFunctions03.html