OT: Uning units in Excel spreadsheets.

When I am solving equations I prefer to keep my units in the equation as a part of the equation. This helps keep one from pulling a NASA.

Eg: 25*lb * 10*feet * 12*inches/feet = 3000 in-lb.

However, I do not know of a clean way to do this in Excel. I've lobied for mathcad, but it ain't happening. Does anyone know of any excel tips, tricks, or add ons?

AFAIK, you just have to do this in your head as you develop the formulas.

I've argued this exact point with several of the teachers in the HS where my kids go. They don't teach using units anymore... My daughter was all frustrated with some physics homework awhile back. She was trying to work the problem modelling it after the examples the teacher used in class and she kept getting the wrong answer (according to the answers in the back of the book) When I reviewed what she was doing, I found that the instructor's in-class example was wrong. If he would have carried the units into the problem, he would have quickly seen that. I confronted him on it and he just kinda laughed it off. During the rest of the school year, my daughter caught him making lots of mistakes after she learned to work the problems through with the units.

I've taught my kids that if they'll work through with the units, they'll at least know if they have the right type of answer -- if they don't have the right units, it doesn't matter how well they do the mechanics of the math -- it's still the wrong answer. At least if they have the right units, there's a chance they have the right answer.

I don't know anyway of doing that in Excel. I still use an old copy of MathCad 3 I think for Windows.

OZEE said:

At least if they have the right units, there's a chance they have the right answer.

Click to expand...

Quoted for truth.

If the units are right then there is a very high probability that the problem is set up correctly and the algebra is correct - there might be an arithmetic mistake but its less likely.

Back in Freshman physics in college all of our problems had to use a format they called 3DBeSNUB. It stood for Describe, Diagram, Define, Basic Equation, Solution (algebraic), Numeric (numbers weren't plugged in until the end), Unit check, and Ballpark check. Any problem that didn't follow this format got a zero, even if the answer was right. At first is seeemed like a real pain in the @. But I quickly discovered that I caught a lot of my own mistakes that way and the form has served me well throughout my career.


Now if only MS would get with it on Excel, it could become a much more valuable engineering tool. It really would be nice to enter 3mm into a cell, have it be treated as a unit number, and perform calculations on it. Still hoping someone knows of an add on or something like that.

OZEE said:

I've taught my kids that if they'll work through with the units, they'll at least know if they have the right type of answer

Click to expand...

The good thing about working through with the units, is that if you can't remember the exact formula, but you know your ending units and your beginning units, you can guess your way through the formula just by knowing where your units have to end up.

I did that a few times in HS physics, couldn't remember if it was a square on top or bottom, or if it was divided by something, or multiplied. Knowing what the final units should be helped me remember where the units should be in the equation.


But, I don't believe you can put units in your equations or even in your fields with the numbers in Excel. You could always place them in a small field next to the field you use in your equation though.

http://www.changeunits.com/

Looks like a start.

Thanks Marc. Its still not quite what I'm looking for, but it does look handy. I'm not really after unit conversions so much as making sure my units carry through all the calculations to the final result.

BTW, for everyone's reference:
A set of really good online conversion tools that will convert almost any unit you can think of: http://www.onlineconversion.com/

And here is a really good stand alone converter tool. I keep a shortcut to this one on on my quick launch tool bar. http://joshmadison.com/software/convert/

Alaric said:

Thanks Marc. Its still not quite what I'm looking for, but it does look handy. I'm not really after unit conversions so much as making sure my units carry through all the calculations to the final result.

Click to expand...

When (IF??) you find it let me know. Heck even doing piRsquared I still resort to dialing in a known (ie dia = 2) to get a known (3.14) to ensure I entered the darn foumula correctly.

The place that has helped me the most in life for doing calculations was Navy Nuc school. They beat unit analysis into our heads - real handy with a slipstick.

It is shocking how little our elecmentary education system really prepares us for life.


Dan Bentler

I guess I'm not understanding exactly what you are looking for. And I'm not familiar with Math Cad. But I've used spreadsheets for years to help with my PLC math. What I've always done is have cells that would represent the addresses I'm using in my calculations. Then I have cell formulas that use those cells to do what I hope to expect my program to do.

For example addresses N7:0 thru N7:15 may come from my HMI numeric entries. They could be represented by cells A1 thru A16 and would hold numbers that the operator would enter. Then the cells that I perform calculations on would just have to use cells A1 thru A16 to get the numbers to calculate.

It is still up to me to use the correct numbers, units and formulas. But I have to make sure I do that anyway.

He's looking for a way to input Units (inches, millimeters, feet, pounds, grams, etc.) in the same cell as the Number. He wants to do this, and still have Excel treat the cell as a Number (once you insert text, Excel says the format is text, and you can no longer use it in mathematical formulas).

He'd like to be able to put 50kg in one cell, and 10 m/s^2 in the next. And be able to use them in a force calculation, and letting it keep the units, resulting in 500 kg*m/s^2.

OK. That makes sense. And I think Excel can be made to do that. You can have Excel take text and numbers from two cells (or more) and put them in another. I bet that posting this to an Excel forum may help.

Here is a very good excel discussion board.

http://www.mrexcel.com/board2/index.php

I've been googling around, getting closer, I found this:
http://www.unit-conversion.com/excel_examples.aspx

You can enter multiple unit terms and it understands them, for example kg*m/s^2 could be re-entered as slug*furlongs/fortnight^2 and as long as you were consisntent on the type of units used it would understand it, but it still doesn't let you include the unit in a cell as part of the value itself and have excel treat is as a dimensioned value and not convert it to dumb text.

I had a spreadsheet that I used to use to select the proper servo motor size for an application.

I entered data into one column (B). I set up additional columns for consistent sets of units. I placed a button object on each cell in those columns. Clicking on the button would execute a script that would take the data from the cell in column B of the same row, convert it to each of the different units and put the converted values into the appropriate cells. Any formulas that used the data I entered would select the values from a single column to maintain units consistency.

For example, column C was units of Inches, Ounces, seconds. Column D was Feet, Pounds, Minutes. Column E was Centimeters, grams, seconds. Column F was Meters, Kilograms, minutes. If row 5 was for velocity, and I was entering it in inches per second, I would enter the number into B5 and then click the button on cell C5. The script associated with the button on C5 would copy the number I entered in B5 over to cell C5, convert from Inches per second to Feet per minute and drop that value into D5, convert from Inches per second to cm per second and drop that into E5, etc.

It was a fair amount of work to write all the scripts, but it saved me from making the kind of "NASA" mistake that Alaric cited.

If all you are interested in is having Excel display the unit of the cell the data is entered in then that is easy to do.

Select the cell (or column) and then select "Format Cells". In the first tab select "Custom". Now, enter the number of decimals that you want displayed followed by the unit in quotes;

0.00 "inches/feet"

For example, if you enter 2.25 in the cell it will display as 2.25 inches/feet in the cell.