Excel-If And Function

[lqorri]

Hello,


I would like a function that would output the years on hand by group max column based on the estimated years on hand max column. I have been able to complete the function by doing If, And statement, however it is a long formula. Is there a shorter way to complete with a range?

[TABLE="class: cke_show_border, width: 384"]
<tbody>[TR]
[TD="width: 197"] Estimated Years on Hand Max [/TD]
[TD="width: 187"] Year On Hand by Group Max [/TD]
[/TR]
[TR]
[TD] 3.03[/TD]
[TD]3-4 years[/TD]
[/TR]
[TR]
[TD] 8.65[/TD]
[TD]5+ years[/TD]
[/TR]
[TR]
[TD] 1.21[/TD]
[TD]1-2 years[/TD]
[/TR]
[TR]
[TD] 5.34[/TD]
[TD]5+ years[/TD]
[/TR]
[TR]
[TD] 1.58[/TD]
[TD]1-2 years[/TD]
[/TR]
[TR]
[TD] 7.78[/TD]
[TD]5+ years[/TD]
[/TR]
[TR]
[TD] 0.42[/TD]
[TD]Less then 1 year[/TD]
[/TR]
</tbody>[/TABLE]


thank you for your help

 

[James Lever]

Hi

I would use: =IF(A2>5,"5+",IF(A2<1,"Less Than 1",ROUNDDOWN(A2,0)&" - "&ROUNDUP(A2,0)))&" Years"

 

[lqorri]

thank you