Menu
I have a set of data in Excel and in one column is a estimate (number of weeks)
I want an Excel formula to bucket it into
where if the value is 0 - 10 then put it Small. If the value is 10 - 20 put it in Medium, etc . . .
if there any elegant way of doing it besides having nested if statements all put together?
956
In the first column, enter the start time for the bucket. In column two, enter the name of the bucket you want to use. The table must be sorted by the start time, smallest to largest. Finally, configure the VLOOKUP function to look up each time in the bucket table with approximate match.
On the Data tab, in the Outline group, click Group. Then in the Group dialog box, click Rows, and then click OK. Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open. The outline symbols appear beside the group on the screen.
The right tool for that is to create a range with your limits and the corresponding names.
You can then use the vlookup() function, with the 4th parameter set to Trueto create a range lookup.
Note: my PC uses ; as separator, yours might use ,.
Adjust formula according to your regional settings.
165
A nice way to create buckets is the LOOKUP() function.
In this example contains cell A1 is a count of days. The vthe second parameter is a list of values. The third parameter is the list of bucket names.
=LOOKUP(A1,{0,7,14,31,90,180,360},{"0-6","7-13","14-30","31-89","90-179","180-359",">360"})
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
