Solved

excel formula

Posted on 2013-05-11
2
255 Views
Last Modified: 2013-06-06
I an looking for an excel formula

in my xls I have a birthdate field column a, I would like column b to be

U9 if column A > 8/31/2003
U11 if column A > 8/31/2001 and < 9/1/2003
U13 if column A > 8/31/1999 and < 9/1/2001
U15 if column A > 8/31/1997 and < 9/1/1999
HS if column A < 9/1/1999
0
Comment
Question by:Matt Pinkston
  • 2
2 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39158910
You might try a formula like:
=IF(A1="","",LOOKUP(A1, DATE({0,1997,1999,2001,2003},9,1),{"HS","U15","U13","U11","U9"}))

It wasn't clear what the criteria for HS should be from the question. I assumed that it was before 9/1/1997, and that you wrote < 9/1/1999 in error.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39158913
You could also use a VLOOKUP formula using a table in cells N1:O6 like:
Date          Category
0                  HS
9/1/1997      U15
9/1/1999      U13
9/1/2001      U11
9/1/2003      U9

The VLOOKUP formula would be:
=IF(A1="","",VLOOKUP(A1,N$2:O$6,2))

Both methods are shown in the attached file
AgeRatedQ28125399.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now