Best way to call UDF with ranges

[JenniferMurphy]

I have written a UDF to calculate weighted ratings. It can be useful in evaluating alternative products. The user can define a collection of features that they care about, assign each one a range of values (best-worst) and a relative weight. Then for each product being evaluated, they simply fill in the value for each feature and the UDF will calculate a weighted rating.

The UDF works great. But I am having trouble figuring out the best way to call it. Before I get into that, let me demo the UDF a bit.

Here's an example of someone about to buy a car. This user has identified 5 features: (Gas Mileage, Price, Warranty, Reliability Index, and GPS). For a Gas Mileage feature, any car getting 50 mpg or more will get a top rating. Any car getting 20 mpg or less will get a bottom rating. Anything in between is prorated. The Price feature is in reverse order. Any car costing $25K or less will get a top rating. Any car costing $60K or more will get a bottom rating. The GPS feature is binary -- the car either has one or it doesn't.

Here's some sample data and the results:
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Car A
[/TD]
[TD="align: center"]Car B[/TD]
[TD="align: center"]Car C[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Features
[/TD]
[TD="align: center"]Best[/TD]
[TD="align: center"]Worst[/TD]
[TD="align: center"]Wt[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7.98
[/TD]
[TD="align: center"]9.15[/TD]
[TD="align: center"]6.37[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]Gas Mileage[/TD]
[TD="align: center"]50 mpg[/TD]
[TD="align: center"]20 mpg[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]35 mpg[/TD]
[TD="align: center"]43 mpg[/TD]
[TD="align: center"]28 mpg[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Price[/TD]
[TD="align: center"]$25K[/TD]
[TD="align: center"]$60K[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$27K[/TD]
[TD="align: center"]$31K[/TD]
[TD="align: center"]$46K[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Warranty[/TD]
[TD="align: center"]5 Yrs[/TD]
[TD="align: center"]2 Yrs[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3.0 Yrs[/TD]
[TD="align: center"]5.0 Yrs[/TD]
[TD="align: center"]3.0 Yrs[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Reliability Index[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]GPS[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"][/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]End[/TD]
[/TR]
</tbody>[/TABLE]
The syntax of the UDF and the actual call in H4:J4 are

=WtdRtg(Ratings, RtgsMax, RtgsMin, Weights, [ScaleMax|10], [ScaleMin|0], [OmitSw|FALSE])
=wtdrtg(H5:H9,$D$5:$D$9,$E$5:$E$9,$F$5:$F$9)
=wtdrtg(I5:I9,$D$5:$D$9,$E$5:$E$9,$F$5:$F$9)
=wtdrtg(J5:J9,$D$5:$D$9,$E$5:$E$9,$F$5:$F$9)

The first 4 parameters are ranges. The Ratings range contains the actual ratings for that product. The RtgsMax range contains the Best values. The RtgsMin range contains the Worst values. And the Weights range contains the relative weights.

The optional ScaleMax & ScaleMin parameters contain the scale that all of the ratings will be mapped onto and they default to a 0-10 scale. The OmitSw parameter specifies whether missing ratings are to be omitted or assigned an average rating.

This all works, but the calling expression shown above is error prone and obtuse. If I want to add a feature, I have to be careful where and how I add it or the expression will not update correctly.

Option #2

An alternative is to name the ranges. If I assign:

• D5:D9 = BestRange
• E5:E9 = WorstRange
• F5:F9 = WtRange
• H5:H9 = ProdARange
• I5:I9 = ProdBRange
• J5:J9 = ProdCRange

then I can code:

=wtdrtg(ProdARange,BestRange,WorstRange,WtRange)
=wtdrtg(ProdBRange,BestRange,WorstRange,WtRange)
=wtdrtg(ProdCRange,BestRange,WorstRange,WtRange)

This is far superior to the first option in readability, but it has exactly the same problem with adding features.

Option #3

A third option is to assign names to the cells just above and below each range and then use the offset function to address and actual range. To make this work, I would assign these names:

• D4 = BestTop
• D10 = BestBot
• E4 = WorstTop
• E10 = WorstBot
• ... etc ...

This leads to this abomination:

=wtdrtg(OFFSET(ProdATop,1,0):OFFSET(ProdABot,-1,0),OFFSET(BestTop,1,0):OFFSET(BestBot,-1,0),OFFSET(WorstTop,1,0):OFFSET(WorstBot,-1,0),OFFSET(WtTop,1,0):OFFSET(

This avoids all of the problems with adding features, but it is a nightmare to code -- highly error prone and unreadable.

Option #4

A fourth option is to expand the named ranges in option #2 to include the cells immediately above and below the actual range. Then change the UDF code to ignore these two cells.

I think that's the path I will take unless someone can suggest something better.

Any suggestions?

Thanks

 

[Russell Hauf]

So a little hard to tell based on the info you gave how you will use this going forward. If you will have say a template, where the user (or you) will always put the data in the same place (the top starts in the same cell), then you could create dynamic named ranges to hold the data, right? So instead of using Top and Bottom values, use a classic named range where Excel counts the data and returns the entire range for you based on the top cell in the range (Features, Best, Worst, Weight). So say you have the following named ranges:

rngFeaturesHead: C4
rngBestHead: D4
rngWorstHead: E4
rngWeightHead: F4

You could then create dynamic ranges for these, along the lines of:

rngFeatures: =OFFSET(rngFeaturesHead,1,,COUNTA(Sheet1!$C$5:$C$100))
rngBest: =OFFSET(rngBestHead,1,,COUNTA(Sheet1!$C$5:$C$100))
rngWorst: etc.
rngWeight etc.

It isn't super elegant with the C5:C100 (you can use whatever you think the max will be instead of 100), but the user wouldn't see it.

Anyway, my 2 cents with the information as I understand it. Have fun!

 

[shg]

I think a UDF is overkill ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]

7.98​

[/td][td="bgcolor:#E5E5E5"]

9.15​

[/td][td="bgcolor:#E5E5E5"]

6.37​

[/td][td][/td][td]E1: =10 * SUMPRODUCT((tbl myCol - Worst) / (Best - Worst) * Wgt) / SUM(Wgt)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Features​

[/td][td="bgcolor:#F3F3F3"]

Best​

[/td][td="bgcolor:#F3F3F3"]

Worst​

[/td][td="bgcolor:#F3F3F3"]

Wgt​

[/td][td="bgcolor:#F3F3F3"]

Car A​

[/td][td="bgcolor:#F3F3F3"]

Car B​

[/td][td="bgcolor:#F3F3F3"]

Car C​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Gas Mileage[/td][td]

50​

[/td][td]

20​

[/td][td]

8​

[/td][td]

35​

[/td][td]

43​

[/td][td]

28​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Price[/td][td]

25000​

[/td][td]

60000​

[/td][td]

7​

[/td][td]

27000​

[/td][td]

31000​

[/td][td]

46000​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Warranty[/td][td]

5​

[/td][td]

2​

[/td][td]

4​

[/td][td]

3​

[/td][td]

5​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Reliability Index[/td][td]

0​

[/td][td]

200​

[/td][td]

10​

[/td][td]

12​

[/td][td]

3​

[/td][td]

21​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]GPS[/td][td]

1​

[/td][td]

0​

[/td][td]

9​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]tbl RefersTo: =INDEX(Sheet1!$B:$B, ROW(Sheet1!$B$2)+1):INDEX(Sheet1!$1:$1048576, MATCH("zzz", Sheet1!$A:$A), MATCH("zzz", Sheet1!$2:$2))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Best RefersTo: =INDEX(tbl, 0, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Worst RefersTo: =INDEX(tbl, 0, 2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Wgt RefersTo: =INDEX(tbl, 0, 3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]myCol RefersToR1C1: =C[/td][/tr]
[/table]

Extend the table as desired.

 

[Russell Hauf]

I agree, shg.

 

[JenniferMurphy]

So a little hard to tell based on the info you gave how you will use this going forward. If you will have say a template, where the user (or you) will always put the data in the same place (the top starts in the same cell), then you could create dynamic named ranges to hold the data, right? So instead of using Top and Bottom values, use a classic named range where Excel counts the data and returns the entire range for you based on the top cell in the range (Features, Best, Worst, Weight). So say you have the following named ranges:

rngFeaturesHead: C4
rngBestHead: D4
rngWorstHead: E4
rngWeightHead: F4

You could then create dynamic ranges for these, along the lines of:

rngFeatures: =OFFSET(rngFeaturesHead,1,,COUNTA(Sheet1!$C$5:$C$100))
rngBest: =OFFSET(rngBestHead,1,,COUNTA(Sheet1!$C$5:$C$100))
rngWorst: etc.
rngWeight etc.

It isn't super elegant with the C5:C100 (you can use whatever you think the max will be instead of 100), but the user wouldn't see it.

Anyway, my 2 cents with the information as I understand it. Have fun!

I did not intend a template, per se. The user (me) would set up the Best, Worst, & Weight ranges anyway they like. They can be rows or columns. They they set up 1 or more ranges for the products.

Your CountA suggestion is clever. I wouldn't have thought of that.

 

[JenniferMurphy]

I think a UDF is overkill ...

Tomatoes, tomahtoes, no?

My tomatoes: I am trying to get to the simplest expression possible.

Your tomahtoes (I think): Avoid UDFs wherever possible.

Regardless, I do need the UDF. It handles a lot of exception conditions and special cases.

1. If a product rating is omitted, it will either skip it or assign it an average value depending on an optional parameter.
2. If a product rating is out of range, it will either assign it a Best/Worst value or continue to scale it outside the range based on another optional parameter.

And I have a couple of other possible options that I might add. I really need the logic of a UDF.

 

[shg]

I really need the logic of a UDF.

=JMUDF(tbl As Range, Best As Range, Worst As Range, Wgt As Range)

There's enough information there to figure out the table orientation and the ratings associated with the calling cell.

 

[Jerry Sullivan]

Tomatoes, tomahtoes, no?

My tomatoes: I am trying to get to the simplest expression possible.

Your tomahtoes (I think): Avoid UDFs wherever possible.

Hi Jennifer,

I didn't interpret shg's to be suggesting "Avoid UDFs wherever possible."

Russel noted that (from the OP alone) it was hard to tell how you were going to use this. Critical factors include whether this needs to be reused in different configurations, and whether this is for your sole use.

Your response in #5, addresses those factors...

I did not intend a template, per se. The user (me) would set up the Best, Worst, & Weight ranges anyway they like. They can be rows or columns. They set up 1 or more ranges for the products.

If this is just for your use (implying you'll have control and understanding of the allowable configurations), you could consider simplifying to:

=wtdrtg(ProdARange,WtTable)

WtTable can be the entire Ratings and Weight range including labels ("Best","Worst","Wgt"). In your OP B2:D7.

Your UDF can use those labels to determine if the orientation is by rows or by columns, and you would have the flexibility to change the order of those rows/columns if needed.

 

[shg]

As an aside, for linear interpolation of an interval, IMO it is more intuitive to put Worst to the left of Best.