New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Formula to auto detect option

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Formula to auto detect option

Postby Justonly » Wed Jun 08, 2016 8:16 pm

Hi Experts!

please help!

kindly refer to my attached picture for easy reference.

in the cell D, i want to formulate such a way that they can detect the duration in Cell C and auto detect the category.
for example, cell C2 is 1 year 10 months, so, at cell D2 it should reflect ">1 yr to ≤ 2 yrs".

thanks!
  • 0

You do not have the required permissions to view the files attached to this post.
Justonly
Rookie
 
Posts: 1
Joined: Jun 8, 2016
Reputation: 0
Excel Version: 2013

Re: Formula to auto detect option

Postby ConneXionLost » Thu Jun 09, 2016 12:04 pm

Using your example as the data layout; in cell D2 try:

Code: Select all
=LOOKUP(INT((B2-A2)/365.25),{0,1,2,3,5},$B$9:$B$13)


Autofill down.

Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 24 guests

cron