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
| |
| |
| |
|
|