From: Bec G (Oz) on 10 Feb 2010 22:53 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 From: Russell Dawson on 10 Feb 2010 23:32 What are you using to get the individual result? -- Russell Dawson Excel Student "Bec G (Oz)" wrote: > 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 > > From: Per Jessen on 10 Feb 2010 23:46 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 On 11 Feb., 04:53, Bec G (Oz) wrote:> 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 From: T. Valko on 11 Feb 2010 00:28 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. -- Biff Microsoft Excel MVP "Bec G (Oz)" wrote in message news:C589E31B-326C-40B7-9201-CEC74633E8FA(a)microsoft.com...> 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 > > From: Joe User on 11 Feb 2010 00:38 "Bec G (Oz)" wrote:> 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 ----- "Bec G (Oz)" wrote:> 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 > >  |  Next  |  Last