No Macro Experience - Need to run a loop (I think?!) over a set of data for multiple rows

You could put all your logic in one big horrible formula, in L5, then copy down:

Formula:

Please Login or Register  to view this content.

Edit: hello, fellow Darlingtonite!

I would make it neater, by using some UDFs:

Please Login or Register  to view this content.

Then in L5, you can enter:

Formula:

Please Login or Register  to view this content.

and copy down. Much neater.

Okay, the calculation for the Defect Extent score is based on the right character of the code. The score increases as the letter 'increases', so we can calculate a score from that letter. In the first version, we take the ASCII code of the right character - for example, the ASCII code for the letter "E" is 69. We wanted that to be 5, so we subtracted 64 from that code:

Formula:

Please Login or Register  to view this content.

Now, we want E to score 3, so we could simply change the number subtracted from the ASCII code, to 66, so that E scores 3:

Formula:

Please Login or Register  to view this content.

But now - the letter A has an ASCII code of 65, so this revised formula would return -1 for code SA. So we need to modify slightly, so that the score returned is never lower than 0:

Formula:

Please Login or Register  to view this content.

This is the corrected formula for the change to your Defect Extent scoring.



The UDF version of this would then become:

Please Login or Register  to view this content.

An alternative approach, in the UDF, which may make it simpler for you to adjust the logic / scores, would be to explicitly state each Defect Extent value, and assign a score manually:

Please Login or Register  to view this content.

I hope you followed all that!! Shout if you need further help with it.

Have a look at the attached file.

This has all the code in a module (Module1). You can access this by pressing Alt-F11, to open the VBE (Visual Basic Editor), then doubleclick on Module1 in the project explorer pane on the left hand side. This is where you can edit the code, so if you wanted to make changes to the rules for each score type, you would do it here.

Then in column L of the worksheet, we use the UDF (user defined function) as the formula:

Formula:

Please Login or Register  to view this content.

The big benefit of managing complex calculations in this way, is that when the logic changes (Defect Extent value scores, for example!), you only have to change the logic in one place, then all the formulae will automatically update based on the revised logic. You don't have to manually change any of the worksheet formulae, so you eliminate the risk of forgetting to update some / making errors in the formula update. I also think laying the logic out in this manner makes it much easier to understand your complex business rules.




(I have added some formulae to generate some random data in columns D:K, for testing. You can just ignore this.)