Charting out all posible combination of multiple ranges

Good morning everyone,

I have developed an evaluation algorithm for residential mortgages, and am having difficulty testing. I am trying to ensure that my scoring model makes sense from every possible scenario. The grading model takes into account multiple factors that most financial institutions would find important when assessing a potential borrower for a loan. Examples of these factors could be an individual's credit score (ranging from 0 - 800), the type of dwelling (multiple options to choose from), etc. In total there are about 20 different factors, each with a minimum of 5 possible options to choose from.

I am trying to create a spreadsheet that lists out all possible combinations of the multiple factors, and trying to input it by hand is a nightmare. To give an example:

Credit Score	Property Dwelling Type	Property Value
100	Townhouse	$350,000
101	Townhouse	$350,000
102	Townhouse	$350,000
103	Townhouse	$350,000
100	Condo	$350,000
101	Condo	$350,000
102	Condo	$350,000
103	Condo	$350,000

Can anyone suggest a function that would make this easier?

Thanks in advance.

Hi General,

Thanks for the reply. To answer your question, each factor has a score attached to it, and each factor has a weighting alotted to it. I'm doing doing it through excel for 2 reasons.

1) I need to see that the scoring model makes sense
2) I can give the excel file to one of my developers to run it through our WCF service (we offer our grading models through an web portal).

By doing so, I can get a direct comparison of my scores vs. the web scores. Basically a way to do a QA test on both the model and the service at the same time.

Is it the best way? probably not, but its the only way I can think of at this point without having to get my guys to build a utility for testing. And the reason for that is that we have some time restrictions to get the model to market.

Basically at the end of the day, the model will produce a single risk score per loan that financial institutions can use for consistency in their underwriting methods.

My initial intention was how to make an enormous list in an automated fashion, but I believe you gave me a much simpler idea. I'm going to create multiple worksheets for each factor and just have the array of ranges for the single factor. I think that would help reduce the lines.