Using an IF Formula to bring back mulitple categories

[edmundmckay]

Hello

I believe I am on Excel 2007-2010 version.

I am looking for guidance on creating a formula which can produce a result of 3 categories.

I have a list of querys which all have the amount of days aged against them from receipt. In my spreadsheet this is Comlumn L. I would like to create a new column, Column M which can index the numbers from Column L into three categories: "Less than 30 Days", "30-90 Days", "90 Days and above".

Currently I have tried =IF(L2>=91,"90 Days above",IF(L2<=30,"Less than 30 Days")) which presents two categories. Where I am struggling with is the inbetween two numbers part, the 30-90 days.

Would you consider posting this on a forum in pursuit of resolving my shortfalls?

best regards

Edmund

 

[Jw4096]

I could be misunderstanding your request but does this do what you wanted?

=IF(L2>=91,"90 Days above",IF(L2<=30,"Less than 30 Days","30-90 Days"))

 

[Trevor G]

Welcome to the forum, take a look at this formula

Excel Workbook
	L	M
1	Days	Results
2	21	Less than 30 Days
3	36	30-90 Days
4	47	30-90 Days
5	89	30-90 Days
6	92	90 Days and above
Sheet1

 

[FormR]

Here's another option.

=LOOKUP(L2,{0,30,91},{"Less than 30 Days","30 to 90 Days","90 Days above"})

 

[edmundmckay]

Hi Trevor

Thank you this is precisely what I was hoping for. I didnt think to use look up for the solution. Could I ask what the brackets {} are which you have placed around 0,30,90 are referred to in excel language?

best regards

Edmund

 

[edmundmckay]

Hi Jw4096


Thank you this works with the excercise at hand.

I appreciate the timely reply and help

best regards

Edmund

 

[edmundmckay]

Thank you Form R too.

All the examples are great to use for the future for my type of work

 

[Trevor G]

They are used for Arrays. Look at this link to help understand how to use them. https://support.office.com/en-za/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7