Help with complex call to UDF or macro

[JenniferMurphy]

I am developing a weighted rating algorithm that I would like to implement in an Excel UDF or macro. It would operate on a sheet like the one below. Each row represents some product I am considering buying. Each column refers to an attribute (property) of that product that I care about. The columns with yellow cells above the table contain properties that are to be included in the rating. The rest are informational only and are not included in the rating.

The yellow cells are in 3 rows: Best, Worst, and Weight. The Best row contains the best value for that property. The Worst contain the worst. The Weight row contains the relative weight to be given to each property in the rating system. These values cannot be calculated by the code. These examples are all Max & Min, but they could be reversed or constants.

The rating code needs access to all of the yellow cells and all of the corresponding cells in each of the table rows in those columns. The code will compare the values in each row against the corresponding Best and Worst values, apply the weighting factor, and return the result to the corresponding cell in the Rating column.

If it's a UDF, the call would be like the examples shown in the Rating column (C). It will need to access each of the values in that row that are to be rated plus all of the cells in yellow and return the result to the calling cell.

If it's a macro, I would call it from a Button control. It will also need to access all of the cells in yellow plus all of the corresponding cells in all of the rows and return the values to all of the cells in the Rating column.

Either way, I want the calls to be independent of the number and position of the cells in yellow. That is, if I add another column of attributes whether it is to be included in the ratings or not, the calls won't have to change. The code will scan the sheet and identify the relevant cells to act on. It probably makes more sense to make it a macro so the sheet scan only has to be done once.

Test 20220320.xlsx
	B	C	D	E	F	G	H	I	J	K
4		Best	$12.99			12 mo	20.00oz	7.68"	3.00"
5		Worst	$17.97			6 mo	12.00oz	6.30"	2.50"
6		Weights	2			1	4	2	2
7	Brand	Rating	Price	Colors	Material	Warranty	Capacity	Height	Width	Comments
8	Brand A	=WtdRtd(???)	$17.97	6	Glass	6 mo	12.00oz	7.50"	2.50"
9	Brand B	=WtdRtd(???)	$13.99	1	Plastic	9 mo	16.00oz	7.68"	3.00"
10	Brand C	=WtdRtd(???)	$13.99	1	Metal	6 mo	12.00oz	6.30"	2.90"
11	Brand D	=WtdRtd(???)	$12.99	3	Metal	12 mo	20.00oz	7.48"	2.95"
12	Brand E	=WtdRtd(???)	$14.99	8	Metal	6 mo	14.00oz	7.20"	2.83"
Wtd Rtg

Cell Formulas
Range	Formula
G4	G4	=MAX(WtdRtg[Warranty])
H4	H4	=MAX(WtdRtg[Capacity])
I4	I4	=MAX(WtdRtg[Height])
J4	J4	=MAX(WtdRtg[Width])
G5	G5	=MIN(WtdRtg[Warranty])
H5	H5	=MIN(WtdRtg[Capacity])
I5	I5	=MIN(WtdRtg[Height])
J5	J5	=MIN(WtdRtg[Width])
D4	D4	=MIN(WtdRtg[Price])
D5	D5	=MAX(WtdRtg[Price])

 

[offthelip]

I have a macro that I use for weighted rating, it is quite flexible because I just have to set two parameters in row 2 (optimal target value) and row 3 ( weighting) in any of Columns A to T to include the values in the calculation. It doesn't take account of your ( Best and Worst case) but I think it could easily be modified to do that:

Sub optimiser()
Worksheets("Summary").Select
' put the target values in row 2 and weighting in row 3 for values to be included in the optimises
constarr = Range(Cells(2, 1), Cells(2, 20))
Weight = Range(Cells(3, 1), Cells(3, 20))
indi = constarr(1, 2)
inarr = Range(Cells(1, 1), Cells(indi - 1, 20))
Range(Cells(1, 21), Cells(indi - 1, 21)) = ""
outarr = Range(Cells(1, 21), Cells(indi - 1, 21))
For i = 4 To indi - 1
  coSum = 0
 For j = 3 To 20
   If IsNumeric(constarr(1, j)) And Not (IsEmpty(constarr(1, j))) Then
     coord = (constarr(1, j) - inarr(i, j)) / constarr(1, j)
     If coord < 0 Then
      coord = 0
     End If
     ' weight the coord
      If IsNumeric(Weight(1, j)) Then
       wcoord = Weight(1, j) * coord
      Else
       wcoord = coord
      End If
     coSum = coSum + (wcoord * wcoord)
   End If
 Next j
 optim = Sqr(coSum)
 outarr(i, 1) = optim
 
Next i
 outarr(1, 1) = "optimiser"

Worksheets("Summary").Select
Range(Cells(1, 21), Cells(indi - 1, 21)) = outarr

 

[JenniferMurphy]

I have a macro that I use for weighted rating, it is quite flexible because I just have to set two parameters in row 2 (optimal target value) and row 3 ( weighting) in any of Columns A to T to include the values in the calculation. It doesn't take account of your ( Best and Worst case) but I think it could easily be modified to do that:

Sub optimiser()
Worksheets("Summary").Select
' put the target values in row 2 and weighting in row 3 for values to be included in the optimises
constarr = Range(Cells(2, 1), Cells(2, 20))
Weight = Range(Cells(3, 1), Cells(3, 20))
indi = constarr(1, 2)
inarr = Range(Cells(1, 1), Cells(indi - 1, 20))
Range(Cells(1, 21), Cells(indi - 1, 21)) = ""
outarr = Range(Cells(1, 21), Cells(indi - 1, 21))
For i = 4 To indi - 1
  coSum = 0
 For j = 3 To 20
   If IsNumeric(constarr(1, j)) And Not (IsEmpty(constarr(1, j))) Then
     coord = (constarr(1, j) - inarr(i, j)) / constarr(1, j)
     If coord < 0 Then
      coord = 0
     End If
     ' weight the coord
      If IsNumeric(Weight(1, j)) Then
       wcoord = Weight(1, j) * coord
      Else
       wcoord = coord
      End If
     coSum = coSum + (wcoord * wcoord)
   End If
 Next j
 optim = Sqr(coSum)
 outarr(i, 1) = optim
 
Next i
 outarr(1, 1) = "optimiser"

Worksheets("Summary").Select
Range(Cells(1, 21), Cells(indi - 1, 21)) = outarr

Wow! Thanks. ?

Could you post a minisheet or screenshot of the calling sheet so I can get an idea of that layout?

 

[offthelip]

Here is one of the worksheets I use it on. Note the 14 in B2 which is used to determine how many rows to process ( 1 less than the rows ) D2 value of 2 is optimal target value for % column, with a weighting of 1, effectively my system has row 2 equal to your BEST, and the worst always being zero, since all my values are positive and the target is greater than the max for all variable.
The lower the value of optimise the better the results

 

[JenniferMurphy]

Here is one of the worksheets I use it on. Note the 14 in B2 which is used to determine how many rows to process ( 1 less than the rows ) D2 value of 2 is optimal target value for % column, with a weighting of 1, effectively my system has row 2 equal to your BEST, and the worst always being zero, since all my values are positive and the target is greater than the max for all variable.
The lower the value of optimise the better the results
View attachment 60658

Perfect. That should keep me busy and out of trouble for a while. ? I'll get back shortly...

Thanks

 

[JenniferMurphy]

Here is one of the worksheets I use it on. . . .

I have a few questions about how your macro works:

1. How to you call the macro? Is there a button control on the worksheet?
2. How does the macro know where the data is (cell range)?
3. What does this statement do: Worksheets("Summary").Select? Is "Summary" a named range in the worksheet? If so, what range?

Thanks

 

[offthelip]

Answers:
1: I just run the macro from the developer tab in this workbook since I don't run it very often. For macros I run more frequently i assign them to an icon on the Quick Access toolbar
2: this bit of the code determines where the data is:

constarr = Range(Cells(2, 1), Cells(2, 20))  ' this says that the target values are in row 2 columns A to T
Weight = Range(Cells(3, 1), Cells(3, 20))    ' this says the weigtings are in  row 3 columns A to T
indi = constarr(1, 2)   ' this states that the last row +1 to process is in B2
inarr = Range(Cells(1, 1), Cells(indi - 1, 20))  ' This picks up all the data from columns A to T up until the lastrow -1
Range(Cells(1, 21), Cells(indi - 1, 21)) = ""  ' this specifies the out put is in coluimn U
outarr = Range(Cells(1, 21), Cells(indi - 1, 21)) ' ditto

3. Worksheets("Summary") is the name of the worksheet I posted. Although many people on here say you should never use .select in macro or change the focus. I actually like to do exactly that for certain macros because I know that when I run this macro:

Worksheets("Summary").Select

this will select the Summary sheet make it the active sheet and thus appear in the excel window and thus display the results of running macro without me needing to select the summary tab.
Note all the addressing is referencing the active sheet which is fine because I have just selected it. So all the ranges as posted above are on the Summary sheet.