Can I use Excel to produce pretty Metric/Imperial conversion scales?

The simplest way is to build the scales using a chart.

Have a series for each measurement system, place each series on a separate axis.
Plot the top of the scale major/minor tick and then use Y error bars set at minus 100% to draw the tick marks.

The data labels are used to display the actual values. You can link data labels to cell values, rather than showing value or category.

Andy,

That was a great answer. I'm not exactly sure how you pulled that off. It produces the effect I was hoping for.

2021-01-03_20-55-37_Speed_Scale.png

In my efforts to tweak the chart it got wonky at times. The zero's on each sale sometimes do not line up. The label ranges do continue past 60 mph if I increase the maximum range value. I added a VBA function to change the chart range if you change the max value listed on the sheet.

If you are still up for the challenge, can you help me make the chart work dynamically? I have included a OneDrive link to an updated version. (I was unable to attach a file.)

tinyurl.com/y9xp39ac

Ben
Pittsburgh, PA
USA

There are instructions at the top of the page explaining how to attach your sample workbook.

I think the problem is that you didn't read and follow the instructions!!!

You cannot post a LINK, but I did not ask you for a link. Try reading the instructions again.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

You already have the two horizontal scales adjusting the maximum so you just need to use some named ranges to provide the ranges to use for x,y and labels.

Use Formulas > Name Manager. to view the new named ranges that have been added.

The data is restricted to 101 data points, unless you extend the formula you currently have.

Currently the tickmark labels are shown above/below the tickmark value, which has different lengths depending on the specific tickmark. This gives the appearence of Major, Minor ticklabels.
If you want the labels to all be on the same vertical level then it would be best to add another series for each scale. The x values would be same as current but the y values would be a single fixed value.