Solved

excel formula

Posted on 2013-05-11
2
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…

734 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