Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation - UPDATE FROM REQUESTER
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation - UPDATE FROM REQUESTER
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation - UPDATE FROM REQUESTER
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation - UPDATE FROM REQUESTER
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Embedded If Limitation - UPDATE FROM REQUESTER
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Limitation in SUMPRODUCT | Excel Worksheet Functions | |||
Row Limitation | Excel Discussion (Misc queries) | |||
Way around row limitation | Excel Discussion (Misc queries) | |||
Function limitation | Excel Worksheet Functions | |||
Excel Limitation | Excel Worksheet Functions |