Idea: Formulae - Custom Bucket intervals?

starsky51 · August 29, 2013 11:00AM

I like the simplicity of the BUCKET function, but the buckets we require tend to be irregular (eg. 0 to 10, 10 to 25, 25 to 50, 50+). The only way I can see of creating these buckets is with nested IFs. Is there a better way of doing this? If not, could the BUCKET function be extended?

tjbate · August 29, 2013 11:59AM

Dave - Yes, at the moment you have to do it with IFs, but the syntax is not that complicated, even with lots of intervals...see the first two examples here:

http://www.visokio.com/kb/useful-formulae

steve · August 30, 2013 3:41AM

In fact the first example (which has a missing bracket or two):
=IF([SourceColumn]=null,null,IF[SourceColumn]<50,"0-50",IF[SourceColumn]<500,"50-500",">500"))

can be written more simply:


IF(
  [SourceColumn]=null, null,
  [SourceColumn]<50, "0-50",
  [SourceColumn]<500, "50-500",
  ">500"
)

starsky51 · August 30, 2013 4:49AM

Thanks Steve. That makes it a lot easier to read.

Welcome!

Categories

Ideas Parade

Tagged