Just a quick question about spreadsheets, MS Excel in particular...

It would be useful, but is probably logically impossible to accomplish within a spreadsheet, if a cell value could be a range, i.e. between 1 and 2, or 1-2.

Is this possible? If not, what are some ways to accomplish the same thing? For example, I want to work with a tolerance which is a range 1.076-1.080.

I figure the only practical way is to input the low and high side of the range in separate cells, and to compare data using if less than, if greater than functions, etc.

What are some ways you are accomplishing this within MS Excel?

As far as I'm aware the value in the cell has to be a unique value.

As you said the most logical (and probably easiest way) is to set your boundaries in two cells and then use a nested "IF" statement

=if("Entered_value<Lower limit,value if true, if("Entered_value>Upper limit, value if true, value if false)

or alternativley set your nominal and then a tolerance and set a "IF" statement as

=if("Entered_value<Nominal - Tolerance,value if true, if("Entered_value>Nominal+tolerance, value if true, value if false).

Please note these formuals sound right in my head, but I have been known to make the odd mistake :D

hah

Thank you so much.

I guess I can't see how a range in one cell would work for a spreadsheet. Must be why it's not possible. Just thought I'd check before doing any work using the other strategy.

Speaking of which, are there any other strategies people would like to share?

I'm not sure how it would apply to what you are trying to do, but you can set multiple constraints on a cell, through using the "Solver" plug in (Tools/Add In's/Solver add in), it's more for "Goal seek" scenarios through iterations though, might be worth a peek none the less

Thanks The Moose

Actually, what I'm trying to do is create a format for inputting specifications of mechanical drawings. This thread is part of an on-going brainstorming session for how to accomplish this, and complete databasing of specifications.

This would be of immediate use in generating check sheets, ISIR paperwork, etc. Conversions would be accomplished properly in every instance and mistakes and double-work would be eliminated.

On the long term, it would be possible to data collect inspection results, which would open up doors to other worlds in my opinion.

Probably the data entry, check sheet generation, and ISIR report generation are the only things that can be accomplished within the scope of MS Excel itself... the databasing would probably need something more powerful than MS Access, which allows only one person to view the database at a time.

Again, thanks so much for the shared knowledge of MS Excel.

A lot of great Excel info can be found at these sites:

Jon Peltier's Excel Page (http://peltiertech.com/Excel/index.html)
Contextures - Excel Tips and Techniques (http://www.contextures.com/tiptech.html)
My Excel Pages -- David McRitchie (http://www.mvps.org/dmcritchie/excel/excel.htm)
Pearson Software Consulting, LLC - Your Source For Expert Excel And VBA Consulting
(http://www.cpearson.com/excel.htm) Nick Hodge's Excel Pages (http://www.nickhodge.co.uk)
AJP Excel Information (http://www.andypope.info/index.htm)

I hope this helps.

atetsade, you could possibly use some string manipulation functions. Is this what you are looking for?

Enter the text representing the range, say "1.076-1.280" in Cell A1 (without Quotes).
(I have used TRIM but, spaces should not matter. It could be " 1.076 - 1.280 " )

Then
First Number (to the left of "-") is:
=TRIM(MID(A1,1,SEARCH("-",A1,1)-1))

Second Number (to the right of "-") is:
=TRIM(MID(A1,SEARCH("-",A1,1)+1,100))

Range (difference) is:
=ABS(TRIM(MID(A1,SEARCH("-",A1,1)+1,100))-TRIM(MID(A1,1,SEARCH("-",A1,1)-1)))

You could remove the ABS if you do not need the absolute value.

Is this possible? If not, what are some ways to accomplish the same thing? For example, I want to work with a tolerance which is a range 1.076-1.080.

I figure the only practical way is to input the low and high side of the range in separate cells, and to compare data using if less than, if greater than functions, etc.

What are some ways you are accomplishing this within MS Excel?

Here is the real low tech way.....

On many occasions I must input my 1st Article Data into a customers spread sheet. The columns are set for nominal, and a seperate column for the plus and the minus. If you are using something similar, use 1.078 for nominal with plus/minus fields at .002.

This is possible using the Left(), Mid() and Right() functions in Excel - but you will probably have to get into some visual basic coding. I've done it in access (can't really remember why) to strip parts out of strings.

You would enter the range as a text string in one cell - lets assume it is cell A1. For example '1.5-2.0

To extract the 1.5, you would use the equation: =Left(A1,3)
This would return 1.5 - the left most three characters.

You might have to use an additional function to convert it back to a number. Left and Right would require that the numbers always be a fixed number of characters.

If your numbers are not always a fixed length, it gets more complicated. I've done it in Access by finding the length of the string (Len function) then using the Mid() function to move one character at a time, looking for my break character - which in this case is the dash (-). I think the Find() function in Excel might do the same thing. If the example is:

1.375-2.5

You would find the location of "-" with a loop or Find() - in this case it is position 6. The left number would be Position 6 minus 1. Right number would be the Length minus Position 6. So,
Left(A1,5) = 1.375
Right (A1,3) = 2.5

You would be much better off (IMO) using two cells to store the numbers. If you need to have it in one cell, you could just string them together. If A1 is 1.375 and B1 is 2.5, enter the formula =A1 & " - " & B1 into C1

Here is the real low tech way.....

On many occasions I must input my 1st Article Data into a customers spread sheet. The columns are set for nominal, and a seperate column for the plus and the minus. If you are using something similar, use 1.078 for nominal with plus/minus fields at .002.In fact this should be the preferred method IMO - entering different values in different cells - unless you have a constraint that you are importing data into Excel as some preformatted text.

Look into conditional formatting option. You can then put in the value and have the conditional formatting give you different colors of fonts if it is within specfication or outside specification

the databasing would probably need something more powerful than MS Access, which allows only one person to view the database at a time.

Again, thanks so much for the shared knowledge of MS Excel.

A little off the topic but....

You can allow more than one person to view a database at one time.
If you load the database tables onto a server, you can link those tables to any computer with Access on it. We do that here - mainly for report sharing. I am not sure about how it might act if more than one person wants to add information to the table.

James

:topic: I love this forum...... there is no place else (that I know of)
where you can get this kind of input.
Thanks to you all
Sincerly,
Ed