Trying to make a custom conversion program/forumla between custom data.

Hi Everyone, I'm not sure if I'm posting in the right place or not, but I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)

What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)

My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.

My assumption was I would have to input all of those trendlines and define the conversion factor as that, but honestly I have no idea where to start as I am not a programmer :/

Any help would be fantastic, Thanks!

-Sean

Please see the attached. I've created a user defined formula - you can see the code by hitting Alt F11 and going to the Module1 tab. Just add extra conversions and remember to save in .xlsm format.

wow, thanks so much!

is there a way to make the To and From cells become dropdowns? I'll end up having quite a few options to choose from to go back and fourth between them

I'm assuming the To cell is B1 and From is A1? So the user would just type in HRC or HK in the appropriate cell as it is now?

Please have a look at the attached.

I've added a list of units to column A and defined these as a dynamic named range called Units, I've then used this to drive drop downs in columns C and D.

The code has been amended slightly to accommodate this and I've added the trivial case where both sets of units are the same and there is not conversion necessary.

that's 100% perfect, thank you so much for your help

I have my one formula for HV to HRC as

ConvertSpecial = 31.49 + (0.0796683 * FromValue) - (0.0000355432 * (FromValue ^ 2)) - (6728.16 * (FromValue ^ -1))

Is there a way to allow excel to use that same formula to workbackwards to convert HRC to HV? Solve for y rather than x in a sense?

You might need to make use of the Solver add-in to iteratively search for a FromValue for a given ConvertSpecial value. With a polynomial like this, there might be several values which are valid solutions. In principle, this could be incorporated into a user defined function with a suggested starting value as one parameter to improve the chances of getting the valid solution that you want.

how would I do that? I've never used the solver function before. I enabled the add-on but quite honestly i don't know what many of the options within Solver do/mean..

Try this

C1 100

C3 = 31.49 + (0.0796683 * C1) - (0.0000355432 * (C1 ^ 2)) - (6728.16 * (C1 ^ -1))

In Solver, set objective $C$3
To value of (say) 50 and solve.

You should get C1 changing to 514.593446761267 which gives a value of 50.00000787.

is there anyway to display that solved equation (or the rearranged one I guess) to add it to the conversion chart that I already have?