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
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
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
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:
hthIf 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.
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...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks