Changing scales without exhausting Excel ;-)

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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.

R/CCDE
3NameValueFormulas
4ScaleMax10D4: 10
5ScaleMin0D5: 0
6RtgsMax1.6D6: =MAX(C10:C15)
7RtgsMin-4.3D7: =MIN(C10:C15)
8
9RatingsRescaleFormulas
101.610.00D10: =(C10-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin
110.37.80D11: =(C11-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin
12-0.95.76D12: =(C12-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin
13-1.44.92D13: =(C13-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin
14-1.54.75D14: =(C14-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin
15-4.30.00D15: =(C15-RtgsMin)/(RtgsMax-RtgsMin)*(ScaleMax-ScaleMin)+ScaleMin

<tbody>
</tbody>

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:

Code:
=(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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
It will do the work over and over again. You're better off keeping the helper cells.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top