Transforming the Range of Data

[jalevin1]

Hello,

I have two fields with data each ranging from 0 to (potentially) the hundreds of thousands. What I need to accomplish is to re-scale column B so that it matches the range of column A.

For example, if column A values were from 0-50,423 and column B values were from 0-78,534, I'd like to transform the values in column B to equal the range of column A (0-78,534).

Please see the example below example and thank in advance for any suggestions/assistance!


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]24,923[/TD]
[TD]28,460[/TD]
[/TR]
[TR]
[TD]24,539[/TD]
[TD]27,841[/TD]
[/TR]
[TR]
[TD]23,252[/TD]
[TD]17,353[/TD]
[/TR]
[TR]
[TD]23,139[/TD]
[TD]14,641[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

This should do the job.

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="width: 67"][/TD]
[TD="width: 67, align: center"]A[/TD]
[TD="width: 67, align: center"]B[/TD]
[TD="width: 20, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[/TR]
[TR]
[TD="width: 67, align: center"]1[/TD]
[TD="width: 67, align: center"]Original A[/TD]
[TD="width: 67, align: center"]Original B[/TD]
[TD="width: 20, align: center"][/TD]
[TD="width: 64, align: center"]Scaled B[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]18,185[/TD]
[TD="align: right"]28,460[/TD]
[TD][/TD]
[TD="align: right"]18479[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]10,970[/TD]
[TD="align: right"]27,841[/TD]
[TD][/TD]
[TD="align: right"]18077.08[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]12,672[/TD]
[TD="align: right"]17,353[/TD]
[TD][/TD]
[TD="align: right"]11267.26[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]18,479[/TD]
[TD="align: right"]14,641[/TD]
[TD][/TD]
[TD="align: right"]9506.361[/TD]
[/TR]
</tbody>[/TABLE]

In D2 and copied down:

=B2*MAX($A$2:$A$5)/MAX($B$2:$B$5)
​