Prev: Excel 2007 no longer opens documents on desktop or email attachmen
Next: Formulate Entire Sheet
From: Joe User on 11 Feb 2010 01:35
"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
news:49687B20-A226-497B-BC3C-E4DFE782DF14(a)microsoft.com...
> "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
>>
>>

First  |  Prev  | 
Pages: 1 2
Prev: Excel 2007 no longer opens documents on desktop or email attachmen
Next: Formulate Entire Sheet