I have got a spreadsheet with how many £'s have been spent on specific suppliers YTD.
I'm trying to put them into discrete 'buckets', being £0 > £5,000, £5,001 > £10,000, £10,000+.
Whilst I can do this manually, I wondered if the fine folks on here could guide me as to an appropriate formula to be able to do this?
Thanks indeed.
Apologies I'm half awake but pivot tables might be a solution. They're usually good for most things.
what do you mean by 'put them into buckets'? If you want it to just be visible when you look through a list then I would use conditional formatting. If you want to see how many are in each value range then take a look at 'bins', eg.
How to Perform Data Binning in Excel (With Example) - Statology
Yes binning would be the way to go. You can also group values on a pivot table but it's a bit more manual.
That’s really useful thanks. I’ve been looking for a way to do that easily but a whole
As usual with Excel you can also achieve the same thing in several different ways. Using COUNTIFS is a bit more faff but makes it easier to fiddle with the bin ranges.
eg values in A1:A999, then in col B 1:6 you have the bins
0
5
10
20
50
99999999
the C1 =COUNTIFS($A$1:$A$999,">=" & $B1, $A$1:$A$999, "<" & $B2) gives the count of values from 0 to 4.9999999, copy that down and you're good to go. You can then amend the bin boundaries or add bins on the fly rather than going through the analysis toolpak each time. Just remember to start the bins below lowest value in your data and finish above the highest value.
