Nested Greater than or less than Formula

[Bec G (Oz)]

Hi was wondering if anyone can assist. I haev a column of data which has
number of days in them per below

EG in column A
25
10
70
156
65
125
155
2
45
30
20
156
61

And in the next column i want to put the following:

If column A is blank or zero then Column B is blank
If column A is between 1 and 30 Column B is "30 Days"
If column A is between 31 and 60 Column B is "60 Days"
If column A is between 61 and 90 Column B is "90 Days"
If column A is greater than 91 Column B is "120+ Days"

Any help is greatly appreciated as I can get each formula to work
individually just not all together

Thanks

Bec G

 

[Per Jessen]

Hi

Look at this:

=IF(A2=0,"",IF(A2<=30,"30 Days",IF(A2<=60,"60 Days",IF(A2<=90,"90
Days","120+ Days"))))

Regards,
Per

 

[T. Valko]

Try one of these...

=IF(A2=0,"",IF(A2<31,30,IF(A2<61,60,IF(A2<91,90,"120+")))&" days")

=IF(A2=0,"",LOOKUP(A2,{1;31;61;91},{30;60;90;"120+"})&" days")

=IF(A2=0,"",IF(A2>90,"120+",CEILING(A2/30,1)*30)&" days")

Note that in each formula there is a space character after the &" just in
case line wrap removes it.

 

[Joe User]

If column A is blank or zero then Column B is blank
If column A is between 1 and 30 Column B is "30 Days"
If column A is between 31 and 60 Column B is "60 Days"
If column A is between 61 and 90 Column B is "90 Days"
If column A is greater than 91 Column B is "120+ Days"

=LOOKUP(--A1,{0,1,31,61,91},
{"","30 days","60 days","90 days","120+ days"})

The "--" is needed in case A1 contains the null string ("") and only
appears to be blank.


----- original message -----

 

[Joe User]

=LOOKUP(--A1,{0,1,31,61,91},
{"","30 days","60 days","90 days","120+ days"})
The "--" is needed in case A1 contains the null string ("")
and only appears to be blank.

D'oh. The "--" does not handle that case correctly. I should have written:

=LOOKUP(N(A1),{0,1,31,61,91},
{"","30 days","60 days","90 days","120+ days"})


----- original message -----