Need to normalize (or scale) variable data ranges

Hello everyone,

I am working on a ScatterChart Template in Excel 2010, but I didn't place this thread in the "chart" section because I thought it made more sense here...because it's really a formula I need...i think.

My data for the chart needs to be scaled in order to prevent an issue with data range for either the X or Y axis being too large to be useful. For instance, if I have multiple datapoints on the Y axis around 10-20 and a few in the 1000's, the scale of the chart will be "off", so to speak.

I am hoping there is a way to build something that takes the max and min value for my data columns and scales that to be whatever I decide; lets say the entire X/Y axis range I want to show is 0-10. So my highest number in either column will be =10 and the lowest number will be 0. That alone seems easy enough, but scaling the rest of the numbers (while remaining relative) seems very difficult.

First off, does that make sense? Secondly, how hard is this going to be? The fact that the data source will be constantly changing (values, not format) is why I need to scale this.

Thank you !