Help with Nested If Formula

[hdelgado]

Hi -
I need a formula that would look at a value and return a result based on the value. The value I'm trying to define is an Age Group based on an "Age" field.

Like this:

Age Age Group (formula results)
10 0-10 Days
14 11-30 Days
35 31-60 Days
65 61-90 Days
111 91-120 Days
345 >221 Days
(see attached)

Thanks in advance!
Helene

[mehmetcik]

Formula for C2 and fill down =LOOKUP(A2,{0,10,14,35,65,111,345},{"0-10","0-10","11-30","31-60","61-90","91-120","345"}) & " Days"

[hdelgado]

Hi -
I did not explain what I wanted correctly. Sorry about that. I need the formula to evaluate the number in the cell A2 (it could be any number between 0 and more than 221 days) and return the age group based on that number.

I'm attaching another example file.

Thanks!

[wk9128]

Response #3

Age data can be negative and greater than 130 ? Please post an excel file with an example.

The answer you expect is the number or the #2 age range

[hdelgado]

Hi -
The number can be greater than 30. The answer or formula should be in column B (age range). I've attached a new file.

Thank you!

[Pepe Le Mokko]

Your last group ends with 120 days and the next condition is "larger than 221 days"?
What happens between 120 and 221 ? ( or did you mean 121) ?

[wk9128]

B2 cell formula

[hdelgado]

I missed one. The next condition should be 121-220 days and the last >221 days.

Thank you!

[hdelgado]

Your last group ends with 120 days and the next condition is "larger than 221 days"?
What happens between 120 and 221 ? ( or did you mean 121) ?

I missed one. The next condition should be 121-220 days and the last >221 days.

Thank you!

[hdelgado]

B2 cell formula

Thank you!

[Bo_Ry]

Please try at B2

Please Login or Register  to view this content.

[hdelgado]

Thank you!

[wk9128]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.