+ Reply to Thread
Results 1 to 3 of 3

Buckets with if formula

  1. #1
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Buckets with if formula

    Hi can someone please give me a formula to form the below mentioned buckets.

    Less Than or Equal to 3 Days
    4 to 10 Days
    11 to 20 Days
    21 to 30 Days
    Greater Than 30 Days
    thank you

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    Assuming the reference cell is A1 and you want the the 'budget' range to be returned (chage as required) the following should do the trick:

    =IF(A1<=3,"Bucket1",IF(AND(A1>=4,A1<=10),"Bucket2",IF(AND(A1>=11,A1<=20),"Bucket3",IF(AND(A1>=21,A1<=30),"Bucket4","Bucket5"))))

    HTH

    Robert

  3. #3
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi all,

    Robert's formula can be shortened by removing all the AND clauses because each of the preceding IF statements makes the check redundant. the formula can be written as:
    =IF(A1<4,"bucket1",IF(A1<11,"bucket2",IF(A1<21,"bucket3",IF(A1<31,"bucket4","bucket5"))))

    edit: as I checked that this had posted properly I realised the above could be shortened slightly more to read:
    ="bucket"&IF(A1<4,1,IF(A1<11,2,IF(A1<21,3,IF(A1<31,4,5))))


    I'm sure that I've seen a better solution than this posted by Daddylonglegs at some stage but I can't remember the principle used or even find it again...

    Anyway, I kept playing around & the below should also work as long as there are no negative values in your list:
    ="bucket"&VLOOKUP(A1,{0,1;4,2;11,3;21,4;31,5},2,TRUE)

    This vlookup uses a literal array (defined by the squiggly brackets) where semi colons are used to separate vertical elements (similar to rows) & commas are used for horizontal elements (similar to columns). The Excel help files explain the use of true in the vlookup with:
    If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.
    hth
    Rob
    Last edited by broro183; 02-01-2008 at 03:49 AM.
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1