Prev: Excel 2007 no longer opens documents on desktop or email attachmen
Next: Formulate Entire Sheet
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) <Bec...(a)discussions.microsoft.com>
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)" <BecGOz(a)discussions.microsoft.com> 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
Pages: 1 2
Prev: Excel 2007 no longer opens documents on desktop or email attachmen
Next: Formulate Entire Sheet