Banding Results

[Ben Hur]

Hi,

I've tried to search to find an answer to this question, but I've
drawn a blank so far. If I've got a column which displays salary
details, is there any easy way to "band" the results into user-defined
ranges (i.e. $0-$5000, $5000-$10000 etc.). Thus far I've been using a
long-winded IF statement, but I'm hampered by the amount of nested IFs
I can use - for the latest spreadsheet I'd need more than 20 IFs to
split the results into the relevant bands.

I can't help thinking there must be an easier way of doing this. Can
anyone help?

BH

 

[Frank Kabel]

Hi
do your ranges always consist of +5000 steps?

 

[Dave Peterson]

Maybe you could use a helper column that only returns the highest value of the
range: 0,5000,10000...

=CEILING(A1,5000)
And drag down:

Or if you need both:
=TEXT(CEILING(A1,5000)-4999,"$#,##0")&"-"&TEXT(CEILING(A1,5000),"$#,##0")

(5000 went in 1-5000 not 5000-10000)