Use and array in a formula to classify value 1

[waubain]

I am working in Excel 2010

I need to create a new column based on a value in Column "C" based on the following classification system:

• urban: 1.0, 1.1, 2.0, 2.1, 3.0, 4.1, 5.1, 7.1, 8.1, 10.1
• large rural: 4.0, 4.2, 5.0, 5.2, 6.0, 6.1
• small rural: 7.0, 7.2, 7.3, 7.4, 8.0, 8.2, 8.3, 8.4, 9.0, 9.1, 9.2
• isolated: 10.0, 10.2, 10.3, 10.4, 10.5, 10.6

I was trying to avoid typing out all the values and tried to use an array value.

=IF(C2 = {1,1.1,2,2.1,3,4.1,5.1,7.1,8.1,10.1}, "URBAN",IF(C2 = {4,4.2,5,5.2,6,6.1}, "LARGE URBAN", IF(C2 = {7,7.2,7.3,7.4,8,8.2,8.3,8.4,9,9.1,9.2}, "SMALL RURAL", IF(C2 = {10,10.2,10.3,10.4,10.5,10.6}, "ISOLATED", "NO CATEGORY"))))

It works if the value in column C is the first value in any of the 4 arrays, otherwise defaults to NO CATEGORY.

Is something like this possible?

Thank you in advance.



You don't know what you don't know...

 

[SkipVought]

Hi,

Where are these array values in your sheet?

I'm having a difficult time understanding what your sheet looks like????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[waubain]

Skip,
The array values are not anywhere on my sheet other than in the formula I was trying to create.
I was trying to create in Column D: =IF the value in C was one of these values then "URBAN", IF ...
Column C should be a value found in one of the 4 classification schemes.
Thank you.

You don't know what you don't know...

 

[Andrzejek]

Have you considered having a little look-up table on other sheet with:

[pre]
Value Classification
1.0 URBAN
1.1 URBAN
2.0 URBAN
2.1 URBAN
3.0 URBAN
4.1 URBAN
5.1 URBAN
7.1 URBAN
8.1 URBAN
10.1 URBAN
4.0 LARGE URBAN
4.2 LARGE URBAN
5.0 LARGE URBAN
5.2 LARGE URBAN
6.0 LARGE URBAN
6.1 LARGE URBAN
7.0 SMALL RURAL
...
[/pre]
Or a few lines of simple VBA code?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Lookup table and an OFFSET() function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[waubain]

I will try the LOOKUP function. I have done VBA in Excel in the distant past, but could probably classify the 46,000+ columns manually before I can get my ranges to come out correctly.

Thanks to you both.

You don't know what you don't know...

 

[Andrzejek]

I know this is not a VBA Forum, but...

If you have your numbers in column C starting in C2 and you want to have your classifications in column D, you just may use this code in standard Module:

Public Function MyClass(ByRef sngNumber As Single) As String

Select Case sngNumber
    Case 1, 1.1, 2, 2.1, 3, 4.1, 5.1, 7.1, 8.1, 10.1
        MyClass = "URBAN"
    Case 4, 4.2, 5, 5.2, 6, 6.1
        MyClass = "LARGE URBAN"
    Case 7, 7.2, 7.3, 7.4, 8, 8.2, 8.3, 8.4, 9, 9.1, 9.2
        MyClass = "SMALL RURAL"
    Case 10, 10.2, 10.3, 10.4, 10.5, 10.6
        MyClass = "ISOLATED"
    Case Else
        MyClass = "NO CATEGORY"
End Select

End Function

And in cell D2 type formula [tt]=MYCLASS(C2)[/tt]
Copy and paste this formula down the column D

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Deniall]

If you really want to use an approach like the one you tried, change
C2={1,1.1,2,2.1,3,4.1,5.1,7.1,8.1,10.1}
to
NOT(ISNA(MATCH(C2,{1,1.1,2,2.1,3,4.1,5.1,7.1,8.1,10.1},0)))
then make corresponding changes to your other three tests.

 

[SkipVought]

I would NOT recommend putting data into formulas or VBA code.

Use the table layout as per Andy's suggestion.

Your formula referencing C2, AND using Named Ranges based on values in the TOP row...
[tt]
=SUM(OFFSET(Value,MATCH(C2,Classification,0)-1,0,COUNTIF(Classification,C2),1))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[waubain]

I created the lookup table per Andy's suggestion. A final comment, if the table is created as above, the Values have to be sorted from smallest to highest to give the proper Classification.

Thank you everyone for your suggestions.

You don't know what you don't know...

 

[Deniall]

The values do not have to be sorted if you are using VLOOKUP and you enter FALSE as its fourth argument.