MS Excel -- Can a cell value be a range (1-2)

[apestate]

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?

[The Moose]

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

[apestate]

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?

[The Moose]

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

[apestate]

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.

[Dan Armstrong]

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

Jon Peltier's Excel Page
Contextures - Excel Tips and Techniques
My Excel Pages -- David McRitchie
Pearson Software Consulting, LLC - Your Source For Expert Excel And VBA Consulting
Nick Hodge's Excel Pages
AJP Excel Information

I hope this helps.

[Atul Khandekar]

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.

[CarolX]

Originally Posted by atetsade

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.