Excel Question

I can't help with your exact question, but how about if you put the 'convert from' in A1 and B1 like you said, but put the 'convert to' in other cells, say C1 and D1?

This is probably not the most elegant way, but it should work. I only have Excel 2000, so newer versions may have better tools available. I'll assume you intend to extend this concept to many units conversions.

You are using data validation to get the values for the units in B1 and D1, so you must have tables with these values somewhere else in you spreadsheet. Do the conversion in 2 steps. Use one cell, say Z1, as a temporary holding spot. Put a formula in Z1 to convert the input to some consistent unit of pressure, say psi, by multiplying A1 by a value from a lookup table on B1. Then, in C1, put a formula to convert from psi in Z1 to whatever units are selected in D1 by multiplying Z1 by a value in a lookup table on D1. (I'm thinking 2 lookup tables, each with one column of units (text) and the other with conversion factors (numbers).

Why do you still have excel 2000... Excel 2010 has a lot of new features that would make excel 2000 look like the apple II to the mac book air..

Would you like to contribute to the 'bigg software acquisition fund'? I've used newer versions, but when it comes to forking out the $, 2000 does everything I need at home.

There isn't sufficient intelligence in Excel to do this. It needs to know that Pa ia a unit of pressure, and atm also, and how to inter-relate them. Further, by updating A1, it would disappear up its own exhaust pipe: how many iterations would it need to carry out (rhetorical question)? The intention is just one, though Excel would need to know this.

It can't do it in the way described, though there are innumerable other ways of doing it, as suggested in #1↑.

There is no intelligence in software, its the intelligence of the programmer, and sadly, my intelligence is not up to this specific task..

You could use nested If/then/else tests.

The only way I can see to do it is to do some programming in VBA. I think PW is right in that using just the built-in functions for the cells, excel won't do it.

You are correct.. I used

Private Sub Worksheet_Change(ByVal Target As Range)

and it works beautifully!

It is possible, and I did it.. the secret is a routine by the nameof Worksheet_change..

Here is a very generic version for you to use.

Thanks for the input..

Private Sub Worksheet_Change(ByVal Target As Range) Dim OldUnit As Variant Dim NewUnit As Variant Dim ConversionFactor As Double If Target.Cells.Count = 1 And Target.Column = 2 Then ' Save new units NewUnit = Target ' prevent recalling this code when we update cells Application.EnableEvents = False ' Get old units Application.Undo OldUnit = Target ' put new units back on sheet Target = NewUnit ' work out your conversion factor based on old and new units ConversionFactor = 0.5 ' for testing ' update value Target.Offset(0, -1) = Target.Offset(0, -1) * ConversionFactor Application.EnableEvents = True End If End Sub

One question though:

If this overwrites the original value with the converted value, how do you check your work?

Part of doing calculations/conversions is having enough documented information to check your end result. Or else THIS might happen.

All any computer program can ever do is execute the logic imposed by the user/programer. Internal logic checks, and spell check can only go so far. When I want to check out a new routine, I input either 1 or zero for each of the input data. That way the converted number shows up as the conversion factor and is easily recognizable. Likewise when zero is input it will show or confirm the logic.

This is correct. I am doing this calculation for another company and I have to make sure that I can catch all the possible user errors. I am thinking of scrapping this idea all together and going with what Stewart has suggested and using a table to covert all inputs into standard working units used througout..

By the way, it is an interesting article you have linked to.. Bjarne Stroustrup recently gave a talk about the new C++ standard and good coding practice, and he mentioned this as well...

He suggested that all numeric values used in programming be assigned a unit and he showed a routine that basically checks for correct units all the time.. I would like to find something similar in excel

As engineers, we are always wary of keeping the right units when doing a calculation, but when making a calculation worksheet for other engineers/nonengineers to use, the issue becomes extremely important.

For this and other Excel questions, try:

Google "MrExcel." Or go to MrExcel.com. You should find that Bill Jelen is Mr. Excel; he has a book about Excel. He is the one who will be a presenter at out NE Ohio Computer User's Conference.

Another is one that I often get ads from. ExcelTips by Allen Wyatt. Book name is ExcelTips: Times and Dates. Check store.tips.net/T010154 and T010155.

I got a working conversion by: (1) providing input fields in Excel in one worksheet for the value , starting units, units sought, and results fields for the conversion factor in the units sought, and the final result in the units sought. (2) in a separate worksheet, I prepared a table with the input unit as row label, and output unit as column header of known conversion factors for 10 different units of pressure (listed in alphabetical order for no apparent reason), (3)used the "match" and "address" functions in Excel to find the cell reference to the necessary constant, and (4) used the "indirect" function to get the value from the table. From this point it was a simple calculation in Excel to arrive at the final answer. Works every time, as long as you type in the unit correctly.

May I have a look at what you have done? I can send you my email..

I would simply attach the file, but I have not figured out a way to do that on this blog.

Re: "...have not figured out a way to do that on this blog"

You could upload the file to your personal webspace/file-area (provided by your ISP), and then send a LINK to it via "Personal Message" to any individual, or place the link in a msg here, if you wanted *anyone* to be able to look at it.

((Or, there are many 'File-Sharing' sites available, if you do not have your OWN space available...then, likewise, provide a LINK to it))

Thanks, I sent the file to the interested party. If he wants to share it, it's up to him.