IF, or LOOKUP, or...?

D

Dean

Hi all,

I have been trying to get this formula but I never quite get there;
can anyone help?

I have a list of 14 dates (1870-2000) and want to classify them into
one of 4 groups, pre-1940, pre-1970, 1960-1979, and post-1970.

So I have been trying to use the IF and LOOKUP statements to various
degrees of success but as I say it just isn't quite right. Can anyone
offer any assistance?

Much appreciated and thanks in advance,
Dean
 
T

T. Valko

Are these *full dates* or just year numbers? Excel doesn't recognize dates
before Jan 1 1900.
 
D

Dean

Are these *full dates* or just year numbers? Excel doesn't recognize dates
before Jan 1 1900.
Click to expand...

I am just dealing with year numbers (i.e. 1870, 1920, etc), not full
dates (i.e. July 1, 1950).
 
D

Dean

Ok, you have conflicting ranges:


1965 is both pre-1970 and 1960-1979

And:


1975 is both 1960-1979 and post-1970
Click to expand...

....and that's the rub, isn't it? I wish it weren't so, but it is. Any
suggestions?
 
M

muddan madhu

try this

=IF(C15<1870,"",IF(C15<=1940,"Pre 1940",IF(AND
(C15>1940,C15<=1970),"Pre 1970",IF(AND
(C15>=1960,C15<=1979),"1960-1979",IF(C15>1979,"post 1979","")))))
 
D

Dean

try this

=IF(C15<1870,"",IF(C15<=1940,"Pre 1940",IF(AND
(C15>1940,C15<=1970),"Pre 1970",IF(AND
(C15>=1960,C15<=1979),"1960-1979",IF(C15>1979,"post 1979","")))))
Click to expand...

Not quite; I am getting an error when inserting the formula. Also, the
last category is 'post-1970', not 'post-1979' as in your formula. I
see where you are going with this...
 
D

Dean

Ok, you have conflicting ranges:


1965 is both pre-1970 and 1960-1979

And:


1975 is both 1960-1979 and post-1970
Click to expand...

If it makes any difference in terms of conflicts, my dates will always
be by decade, i.e. 1880, 1970, etc., and never 1961, 1965, etc.
 
T

T. Valko

Define which ranges have precedence.
pre-1970, 1960-1979,
Click to expand...
1965 is both pre-1970 and 1960-1979

Perhaps the correct range would be 1960-1979 because it is a specific range.

Same for:
1960-1979, and post-1970.
Click to expand...
1975 is both 1960-1979 and post-1970

But *you* have to decide. One you do it'll be a snap to come up with a
formula.
 
D

Dean

Define which ranges have precedence.


1965 is both pre-1970 and 1960-1979

Perhaps the correct range would be 1960-1979 because it is a specific range.

Same for:


1975 is both 1960-1979 and post-1970

But *you* have to decide. One you do it'll be a snap to come up with a
formula.
Click to expand...

Thanks; of course you are right about that so I'll have to decide.

One other thing, if my column of dates include 'Mixed' or a zero, how
can I include that in my formula to return either 'Mixed' or 'No date'
respectively? For some reason I have not had any luck with text...
 
R

Rich/rerat

Dean,
Create a Vlookup table in D1:E26 on the same sheet. Or create it on a separate
sheet, you will need to indicate the table location on the 2nd sheet in the
formula.

D E
1 Year Category
2 0 No Date
3 1870 Pre 1940
4 1940 Pre 1970
5 1959 Pre 1970
6 1960 1960-1979
7 1961 1960-1979
8 1962 1960-1979
THROUGH (List each year between 1960-1979)
23 1977 1960-1979
24 1978 1960-1979
25 1979 1960-1979
26 1980 Post 1980

In A1: Date
In B1: Category
Starting in A2: Your Data
In B2 the following formula and drag down column B as far as you need to:
=if($A2="Mixed Dates","Mixed Dates",VLOOKUP($A2,$D$2:$E$26,2)


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Hi all,

I have been trying to get this formula but I never quite get there;
can anyone help?

I have a list of 14 dates (1870-2000) and want to classify them into
one of 4 groups, pre-1940, pre-1970, 1960-1979, and post-1970.

So I have been trying to use the IF and LOOKUP statements to various
degrees of success but as I say it just isn't quite right. Can anyone
offer any assistance?

Much appreciated and thanks in advance,
Dean
 
R

Rich/rerat

Dean,
Just a follow up. If your Data is set up like this:

A1=Item
B1= Date
C1=Category

The formula should look like this starting in Category (C2) cell:
=IF($A2="","",IF($B2="Mixed Dates","Mixed Dates",VLOOKUP($B2,$D$2:$E$26,2)))
And Drag down to fill the rest of the column as needed.

This will leave the Category Cell "empty" until you place something in the Item
cell for that record.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Dean,
Create a Vlookup table in D1:E26 on the same sheet. Or create it on a separate
sheet, you will need to indicate the table location on the 2nd sheet in the
formula.

D E
1 Year Category
2 0 No Date
3 1870 Pre 1940
4 1940 Pre 1970
5 1959 Pre 1970
6 1960 1960-1979
7 1961 1960-1979
8 1962 1960-1979
THROUGH (List each year between 1960-1979)
23 1977 1960-1979
24 1978 1960-1979
25 1979 1960-1979
26 1980 Post 1980

In A1: Date
In B1: Category
Starting in A2: Your Data
In B2 the following formula and drag down column B as far as you need to:
=if($A2="Mixed Dates","Mixed Dates",VLOOKUP($A2,$D$2:$E$26,2)


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Hi all,

I have been trying to get this formula but I never quite get there;
can anyone help?

I have a list of 14 dates (1870-2000) and want to classify them into
one of 4 groups, pre-1940, pre-1970, 1960-1979, and post-1970.

So I have been trying to use the IF and LOOKUP statements to various
degrees of success but as I say it just isn't quite right. Can anyone
offer any assistance?

Much appreciated and thanks in advance,
Dean
 
T

T. Valko

OK, try this....

I've given 1960-1979 precedence over pre-1970.

Here's the breakdown:

Empty cell or numeric 0 = No date
Mixed = Mixed
1870 to 1939 = pre-1940
1940 to 1959 = pre-1970
1960 to 1979 = 1960-1979
1980 onward = post-1970

=IF(OR(A1={0,""}),"No
date",IF(A1="Mixed","Mixed",IF(A1<1940,"pre-1940",IF(AND(A1>=1960,A1<=1979),"1960-1979",IF(A1<1970,"pre-1970","post-1970")))))
 
D

Dean

OK, try this....

I've given 1960-1979 precedence over pre-1970.

Here's the breakdown:

Empty cell or numeric 0 = No date
Mixed = Mixed
1870 to 1939 = pre-1940
1940 to 1959 = pre-1970
1960 to 1979 = 1960-1979
1980 onward = post-1970

=IF(OR(A1={0,""}),"No
date",IF(A1="Mixed","Mixed",IF(A1<1940,"pre-1940",IF(AND(A1>=1960,A1<=1979),"1960-1979",IF(A1<1970,"pre-1970","post-1970")))))
Click to expand...

Excellent work...both formulas work perfectly. Thanks for your help!
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


OK, try this....

I've given 1960-1979 precedence over pre-1970.

Here's the breakdown:

Empty cell or numeric 0 = No date
Mixed = Mixed
1870 to 1939 = pre-1940
1940 to 1959 = pre-1970
1960 to 1979 = 1960-1979
1980 onward = post-1970

=IF(OR(A1={0,""}),"No
date",IF(A1="Mixed","Mixed",IF(A1<1940,"pre-1940",IF(AND(A1>=1960,A1<=1979),"1960-1979",IF(A1<1970,"pre-1970","post-1970")))))
Click to expand...

Excellent work...both formulas work perfectly. Thanks for your help!
 
Top