IF, or LOOKUP, or...?

  • Thread starter Thread starter Dean
  • Start date Start date
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
 
Are these *full dates* or just year numbers? Excel doesn't recognize dates
before Jan 1 1900.
 
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).
 
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?
 
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","")))))
 
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...
 
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.
 
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.
 
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...
 
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
 
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
 
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")))))
 
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!
 
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!
 
Back
Top