Hi All,

Im using a nested if statement to age documents based upon the number of
days old a document is. Ive been using this formula for months and today
the business has decided to change the age buckets so ive simply added more
nested ifs to my formula. However, the cell displays the formula and not the
result whereas before it used to display the reult. All I have done is click
in cell, press F2 and edited formula. Is there a maximum number of nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150 Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way to do
this?
I Can use a vlookup but the number of days old is potentially limitless and
i dont want to create a lookup table that big.
--
Rob Gaffney

Update - its ok, I found a fault with formula. However, if anybody can find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number of
days old a document is. Ive been using this formula for months and today
the business has decided to change the age buckets so ive simply added more
nested ifs to my formula. However, the cell displays the formula and not the
result whereas before it used to display the reult. All I have done is click
in cell, press F2 and edited formula. Is there a maximum number of nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150 Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way to do
this?
I Can use a vlookup but the number of days old is potentially limitless and
i dont want to create a lookup table that big.
--
Rob Gaffney

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply added
more
nested ifs to my formula. However, the cell displays the formula and not
the
result whereas before it used to display the reult. All I have done is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way to
do
this?
I Can use a vlookup but the number of days old is potentially limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney

Hi Roger.
All these years ive been using that optional 4th character believing it to
be mandatory. I ves tested your theory and works liek a charm . Much neater
than nested ifs. Thanks for your help.
--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply added
more
nested ifs to my formula. However, the cell displays the formula and not
the
result whereas before it used to display the reult. All I have done is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way to
do
this?
I Can use a vlookup but the number of days old is potentially limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney

It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

This formula works as well

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))

and is better IMO as it explicitly states your intent.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Gaffnr" wrote in message
...
Hi Roger.
All these years ive been using that optional 4th character believing it to
be mandatory. I ves tested your theory and works liek a charm . Much
neater
than nested ifs. Thanks for your help.
--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number
of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply added
more
nested ifs to my formula. However, the cell displays the formula and
not
the
result whereas before it used to display the reult. All I have done
is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way
to
do
this?
I Can use a vlookup but the number of days old is potentially
limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney

Hi Bob

Whilst I agree totally with
It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

I have never found the inclusion of TRUE make's the intention clearer.
Inherently this "appears" backwards to me (simple mind<bg) as TRUE to me
would seem to imply an Exact match, whereas FALSE is the condition that
requires the match to be Exact

I agree it is lazy to leave out the 4th parameter, but I prefer to use 1 and
0 rather than TRUE or FALSE.


--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

This formula works as well

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))

and is better IMO as it explicitly states your intent.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Gaffnr" wrote in message
...
Hi Roger.
All these years ive been using that optional 4th character believing it
to
be mandatory. I ves tested your theory and works liek a charm . Much
neater
than nested ifs. Thanks for your help.
--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody
can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number
of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply
added
more
nested ifs to my formula. However, the cell displays the formula and
not
the
result whereas before it used to display the reult. All I have done
is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way
to
do
this?
I Can use a vlookup but the number of days old is potentially
limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney

Hi Roger,

In the Dutch version of Excel the 4th argument is called Approximate (as a verb). Then the TRUE and FALSE make more sense.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ...
| Hi Bob
|
| Whilst I agree totally with
| It is not so much of omitting the 4th argument that is why it works, but
| rather that its default value is TRUE.
|
| I have never found the inclusion of TRUE make's the intention clearer.
| Inherently this "appears" backwards to me (simple mind<bg) as TRUE to me
| would seem to imply an Exact match, whereas FALSE is the condition that
| requires the match to be Exact
|
| I agree it is lazy to leave out the 4th parameter, but I prefer to use 1 and
| 0 rather than TRUE or FALSE.
|
|
| --
| Regards
| Roger Govier
|
| "Bob Phillips" wrote in message
| ...
| It is not so much of omitting the 4th argument that is why it works, but
| rather that its default value is TRUE.
|
| This formula works as well
|
| =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))
|
| and is better IMO as it explicitly states your intent.
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
| addy)
|
|
|
| "Gaffnr" wrote in message
| ...
| Hi Roger.
| All these years ive been using that optional 4th character believing it
| to
| be mandatory. I ves tested your theory and works liek a charm . Much
| neater
| than nested ifs. Thanks for your help.
| --
| Rob Gaffney
|
|
| "Roger Govier" wrote:
|
| Hi Rob
|
| Setup a table on say sheet2 in columns A and B
| 0 Rob
| 51 Dennis
| 999 Roger
|
| =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))
|
| With a value 57, Dennis would be returned - not Rob.
| The Vlookup formula (without the optional 4 th parameter of False) will
| return the value which does not exceed the looked-up value.
|
| You can have as many ranges as you wish, just ensure that the first on
| starts with 0
|
|
| --
| Regards
| Roger Govier
|
| "Gaffnr" wrote in message
| ...
| Update - its ok, I found a fault with formula. However, if anybody
| can
| find
| a smarter way to do this, id be grateful.
|
| Im thinking a vlookup with a range?
|
| i.e. if cell A1 = 57
| do a vlookup on 57
| in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
| and return the value Rob
|
| --
| Rob Gaffney
|
|
| "Gaffnr" wrote:
|
| Hi All,
|
| Im using a nested if statement to age documents based upon the number
| of
| days old a document is. Ive been using this formula for months and
| today
| the business has decided to change the age buckets so ive simply
| added
| more
| nested ifs to my formula. However, the cell displays the formula and
| not
| the
| result whereas before it used to display the reult. All I have done
| is
| click
| in cell, press F2 and edited formula. Is there a maximum number of
| nested
| if's?
|
| My formula is below
|
| =IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
| Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
| Days",IF(K3<366,"151
| to 365 Days","365+Days"))))))
|
|
| If there is a maximum no. of nests, can anubody think of another way
| to
| do
| this?
| I Can use a vlookup but the number of days old is potentially
| limitless
| and
| i dont want to create a lookup table that big.
| --
| Rob Gaffney
|
|
|

Hi Roger,

I can't say TRUE suggests an exact match to me, nor FALSE not exact, as it
is just an argument. Rather, it explicitly states what course you wish to
take, but I know what you mean. To my mind, if you know want an argument
does it is clear, if you don't then look it up.

I am just very anti omitting values and leaving to defaults as a fundamental
principle.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Bob

Whilst I agree totally with
It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

I have never found the inclusion of TRUE make's the intention clearer.
Inherently this "appears" backwards to me (simple mind<bg) as TRUE to me
would seem to imply an Exact match, whereas FALSE is the condition that
requires the match to be Exact

I agree it is lazy to leave out the 4th parameter, but I prefer to use 1
and 0 rather than TRUE or FALSE.


--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

This formula works as well

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))

and is better IMO as it explicitly states your intent.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Gaffnr" wrote in message
...
Hi Roger.
All these years ive been using that optional 4th character believing it
to
be mandatory. I ves tested your theory and works liek a charm . Much
neater
than nested ifs. Thanks for your help.
--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody
can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the
number of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply
added
more
nested ifs to my formula. However, the cell displays the formula
and not
the
result whereas before it used to display the reult. All I have done
is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way
to
do
this?
I Can use a vlookup but the number of days old is potentially
limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney

Yes, that does make more sense Niek.

I do Agree with Bob though, that it is better to be explicit and use all of
the arguments, with whatever is appropriate.
I will cease my lazy ways (or buy the beer in Seattle<bg)

--
Regards
Roger Govier

"Niek Otten" wrote in message
...
Hi Roger,

In the Dutch version of Excel the 4th argument is called Approximate (as a
verb). Then the TRUE and FALSE make more sense.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
| Hi Bob
|
| Whilst I agree totally with
| It is not so much of omitting the 4th argument that is why it works,
but
| rather that its default value is TRUE.
|
| I have never found the inclusion of TRUE make's the intention clearer.
| Inherently this "appears" backwards to me (simple mind<bg) as TRUE to
me
| would seem to imply an Exact match, whereas FALSE is the condition that
| requires the match to be Exact
|
| I agree it is lazy to leave out the 4th parameter, but I prefer to use 1
and
| 0 rather than TRUE or FALSE.
|
|
| --
| Regards
| Roger Govier
|
| "Bob Phillips" wrote in message
| ...
| It is not so much of omitting the 4th argument that is why it works,
but
| rather that its default value is TRUE.
|
| This formula works as well
|
| =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))
|
| and is better IMO as it explicitly states your intent.
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
| addy)
|
|
|
| "Gaffnr" wrote in message
| ...
| Hi Roger.
| All these years ive been using that optional 4th character believing
it
| to
| be mandatory. I ves tested your theory and works liek a charm .
Much
| neater
| than nested ifs. Thanks for your help.
| --
| Rob Gaffney
|
|
| "Roger Govier" wrote:
|
| Hi Rob
|
| Setup a table on say sheet2 in columns A and B
| 0 Rob
| 51 Dennis
| 999 Roger
|
| =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))
|
| With a value 57, Dennis would be returned - not Rob.
| The Vlookup formula (without the optional 4 th parameter of False)
will
| return the value which does not exceed the looked-up value.
|
| You can have as many ranges as you wish, just ensure that the first
on
| starts with 0
|
|
| --
| Regards
| Roger Govier
|
| "Gaffnr" wrote in message
| ...
| Update - its ok, I found a fault with formula. However, if
anybody
| can
| find
| a smarter way to do this, id be grateful.
|
| Im thinking a vlookup with a range?
|
| i.e. if cell A1 = 57
| do a vlookup on 57
| in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
| and return the value Rob
|
| --
| Rob Gaffney
|
|
| "Gaffnr" wrote:
|
| Hi All,
|
| Im using a nested if statement to age documents based upon the
number
| of
| days old a document is. Ive been using this formula for months
and
| today
| the business has decided to change the age buckets so ive simply
| added
| more
| nested ifs to my formula. However, the cell displays the formula
and
| not
| the
| result whereas before it used to display the reult. All I have
done
| is
| click
| in cell, press F2 and edited formula. Is there a maximum number
of
| nested
| if's?
|
| My formula is below
|
| =IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to
90
| Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
| Days",IF(K3<366,"151
| to 365 Days","365+Days"))))))
|
|
| If there is a maximum no. of nests, can anubody think of another
way
| to
| do
| this?
| I Can use a vlookup but the number of days old is potentially
| limitless
| and
| i dont want to create a lookup table that big.
| --
| Rob Gaffney
|
|
|

That's two great ideas in one post, Roger!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ...
| Yes, that does make more sense Niek.
|
| I do Agree with Bob though, that it is better to be explicit and use all of
| the arguments, with whatever is appropriate.
| I will cease my lazy ways (or buy the beer in Seattle<bg)
|
| --
| Regards
| Roger Govier
|
| "Niek Otten" wrote in message
| ...
| Hi Roger,
|
| In the Dutch version of Excel the 4th argument is called Approximate (as a
| verb). Then the TRUE and FALSE make more sense.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
| ...
| | Hi Bob
| |
| | Whilst I agree totally with
| | It is not so much of omitting the 4th argument that is why it works,
| but
| | rather that its default value is TRUE.
| |
| | I have never found the inclusion of TRUE make's the intention clearer.
| | Inherently this "appears" backwards to me (simple mind<bg) as TRUE to
| me
| | would seem to imply an Exact match, whereas FALSE is the condition that
| | requires the match to be Exact
| |
| | I agree it is lazy to leave out the 4th parameter, but I prefer to use 1
| and
| | 0 rather than TRUE or FALSE.
| |
| |
| | --
| | Regards
| | Roger Govier
| |
| | "Bob Phillips" wrote in message
| | ...
| | It is not so much of omitting the 4th argument that is why it works,
| but
| | rather that its default value is TRUE.
| |
| | This formula works as well
| |
| | =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))
| |
| | and is better IMO as it explicitly states your intent.
| |
| | --
| | ---
| | HTH
| |
| | Bob
| |
| |
| | (there's no email, no snail mail, but somewhere should be gmail in my
| | addy)
| |
| |
| |
| | "Gaffnr" wrote in message
| | ...
| | Hi Roger.
| | All these years ive been using that optional 4th character believing
| it
| | to
| | be mandatory. I ves tested your theory and works liek a charm .
| Much
| | neater
| | than nested ifs. Thanks for your help.
| | --
| | Rob Gaffney
| |
| |
| | "Roger Govier" wrote:
| |
| | Hi Rob
| |
| | Setup a table on say sheet2 in columns A and B
| | 0 Rob
| | 51 Dennis
| | 999 Roger
| |
| | =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))
| |
| | With a value 57, Dennis would be returned - not Rob.
| | The Vlookup formula (without the optional 4 th parameter of False)
| will
| | return the value which does not exceed the looked-up value.
| |
| | You can have as many ranges as you wish, just ensure that the first
| on
| | starts with 0
| |
| |
| | --
| | Regards
| | Roger Govier
| |
| | "Gaffnr" wrote in message
| | ...
| | Update - its ok, I found a fault with formula. However, if
| anybody
| | can
| | find
| | a smarter way to do this, id be grateful.
| |
| | Im thinking a vlookup with a range?
| |
| | i.e. if cell A1 = 57
| | do a vlookup on 57
| | in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
| | and return the value Rob
| |
| | --
| | Rob Gaffney
| |
| |
| | "Gaffnr" wrote:
| |
| | Hi All,
| |
| | Im using a nested if statement to age documents based upon the
| number
| | of
| | days old a document is. Ive been using this formula for months
| and
| | today
| | the business has decided to change the age buckets so ive simply
| | added
| | more
| | nested ifs to my formula. However, the cell displays the formula
| and
| | not
| | the
| | result whereas before it used to display the reult. All I have
| done
| | is
| | click
| | in cell, press F2 and edited formula. Is there a maximum number
| of
| | nested
| | if's?
| |
| | My formula is below
| |
| | =IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to
| 90
| | Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
| | Days",IF(K3<366,"151
| | to 365 Days","365+Days"))))))
| |
| |
| | If there is a maximum no. of nests, can anubody think of another
| way
| | to
| | do
| | this?
| | I Can use a vlookup but the number of days old is potentially
| | limitless
| | and
| | i dont want to create a lookup table that big.
| | --
| | Rob Gaffney
| |
| |
| |
|
|

If only I drank ...

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Niek Otten" wrote in message
...
That's two great ideas in one post, Roger!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
| Yes, that does make more sense Niek.
|
| I do Agree with Bob though, that it is better to be explicit and use all
of
| the arguments, with whatever is appropriate.
| I will cease my lazy ways (or buy the beer in Seattle<bg)
|
| --
| Regards
| Roger Govier
|
| "Niek Otten" wrote in message
| ...
| Hi Roger,
|
| In the Dutch version of Excel the 4th argument is called Approximate
(as a
| verb). Then the TRUE and FALSE make more sense.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
| ...
| | Hi Bob
| |
| | Whilst I agree totally with
| | It is not so much of omitting the 4th argument that is why it
works,
| but
| | rather that its default value is TRUE.
| |
| | I have never found the inclusion of TRUE make's the intention
clearer.
| | Inherently this "appears" backwards to me (simple mind<bg) as TRUE
to
| me
| | would seem to imply an Exact match, whereas FALSE is the condition
that
| | requires the match to be Exact
| |
| | I agree it is lazy to leave out the 4th parameter, but I prefer to
use 1
| and
| | 0 rather than TRUE or FALSE.
| |
| |
| | --
| | Regards
| | Roger Govier
| |
| | "Bob Phillips" wrote in message
| | ...
| | It is not so much of omitting the 4th argument that is why it
works,
| but
| | rather that its default value is TRUE.
| |
| | This formula works as well
| |
| | =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))
| |
| | and is better IMO as it explicitly states your intent.
| |
| | --
| | ---
| | HTH
| |
| | Bob
| |
| |
| | (there's no email, no snail mail, but somewhere should be gmail in
my
| | addy)
| |
| |
| |
| | "Gaffnr" wrote in message
| | ...
| | Hi Roger.
| | All these years ive been using that optional 4th character
believing
| it
| | to
| | be mandatory. I ves tested your theory and works liek a charm .
| Much
| | neater
| | than nested ifs. Thanks for your help.
| | --
| | Rob Gaffney
| |
| |
| | "Roger Govier" wrote:
| |
| | Hi Rob
| |
| | Setup a table on say sheet2 in columns A and B
| | 0 Rob
| | 51 Dennis
| | 999 Roger
| |
| | =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))
| |
| | With a value 57, Dennis would be returned - not Rob.
| | The Vlookup formula (without the optional 4 th parameter of
False)
| will
| | return the value which does not exceed the looked-up value.
| |
| | You can have as many ranges as you wish, just ensure that the
first
| on
| | starts with 0
| |
| |
| | --
| | Regards
| | Roger Govier
| |
| | "Gaffnr" wrote in message
| | ...
| | Update - its ok, I found a fault with formula. However, if
| anybody
| | can
| | find
| | a smarter way to do this, id be grateful.
| |
| | Im thinking a vlookup with a range?
| |
| | i.e. if cell A1 = 57
| | do a vlookup on 57
| | in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
| | and return the value Rob
| |
| | --
| | Rob Gaffney
| |
| |
| | "Gaffnr" wrote:
| |
| | Hi All,
| |
| | Im using a nested if statement to age documents based upon
the
| number
| | of
| | days old a document is. Ive been using this formula for
months
| and
| | today
| | the business has decided to change the age buckets so ive
simply
| | added
| | more
| | nested ifs to my formula. However, the cell displays the
formula
| and
| | not
| | the
| | result whereas before it used to display the reult. All I
have
| done
| | is
| | click
| | in cell, press F2 and edited formula. Is there a maximum
number
| of
| | nested
| | if's?
| |
| | My formula is below
| |
| | =IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60
Days",IF(K3<91,"61 to
| 90
| | Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
| | Days",IF(K3<366,"151
| | to 365 Days","365+Days"))))))
| |
| |
| | If there is a maximum no. of nests, can anubody think of
another
| way
| | to
| | do
| | this?
| | I Can use a vlookup but the number of days old is potentially
| | limitless
| | and
| | i dont want to create a lookup table that big.
| | --
| | Rob Gaffney
| |
| |
| |
|
|