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

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

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

Try one of these...

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

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

=IF(A2=0,"",IF(A290,"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
...
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

"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

"Joe User" <joeu2004 wrote:
=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 -----

"Joe User" <joeu2004 wrote in message
...
"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