Changing scales without exhausting Excel ;-)

[JenniferMurphy]

If I have a table with average ratings of a number of products by people who were given the choice of rating them on a -5 to 5 scale. (1-5 thumbs up, 0 thumbs, 1-5 thumbs down.)

These products got generally negative ratings, which makes the relative ratings a little difficult to visualize. To make the ratings a little easier to visualize relative to each other, I want to re-scale them to a dynamic 0-10 scale based on the maximum and minimum ratings. The maximum rating will be changed to "10", the minimum rating to "0", and all of the rest prorated in between.

The actual ratings are in C10:C15. D4 & D5 are the limits of the new scale. D6 & D7 are the max and min of the ratings. The new scale is in D10:D15. The values in C4:C7 are the names assigned to the corresponding cells in column D.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ScaleMax[/TD]
[TD="align: center"]10[/TD]
[TD]D4: 10[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]ScaleMin[/TD]
[TD="align: center"]0[/TD]
[TD]D5: 0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]RtgsMax[/TD]
[TD="align: center"]1.6[/TD]
[TD]D6: =MAX(C10:C15)[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]RtgsMin[/TD]
[TD="align: center"]-4.3[/TD]
[TD]D7: =MIN(C10:C15)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Ratings[/TD]
[TD="align: center"]Rescale[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]1.6[/TD]
[TD="align: center"]10.00[/TD]
[TD]D10: =(C10-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]7.80[/TD]
[TD]D11: =(C11-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]-0.9[/TD]
[TD="align: center"]5.76[/TD]
[TD]D12: =(C12-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]-1.4[/TD]
[TD="align: center"]4.92[/TD]
[TD]D13: =(C13-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]-1.5[/TD]
[TD="align: center"]4.75[/TD]
[TD]D14: =(C14-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]-4.3[/TD]
[TD="align: center"]0.00[/TD]
[TD]D15: =(C15-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin[/TD]
[/TR]
</tbody>[/TABLE]

I have 2 questions:

One: I have calculated the max & min ratings in D6 & D7 so that only has to be done once. If I get rid of those cells and replace those names in the formulas with the formulas in D6 & D7, I get this:

=(C10-MIN(C10:C15))/(MAX(C10:C15)-MIN(C10:C15))*(ScaleMax-ScaleMin)+ScaleMin

Is Excel smart enough to know to only do the Min & Max work once and use it multiple times or will it do the work over and over?

Two: Is there a better way to do this?

Thanks

 

[RoryA]

It will do the work over and over again. You're better off keeping the helper cells.

 

[JenniferMurphy]

It will do the work over and over again. You're better off keeping the helper cells.

That's what I figured. Thanks.