# Buckets with if formula

1. ## 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

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

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

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