Breaking a number down into component ranges

[skAz]

Greetings,


I'm trying to break a number down into a component of ranges.


For example:


Is there an easy way to break a number into a range of values that make it up?


eg.


I have 4 steps (This is a variable number of steps but, for this exmample they are:


5000, 10,000, 15,000, 20,000


I want a function where if I feed it 5001 I get a reply like:


up too 5000 = 5000

up 5000 but less than 10000 = 1


Or if I entered 19000 I get buckets like:

up too 5000 = 5000

over 5000 and up too 10000 = 5000

over 10,000 up to 15000 = 5000

over 15,000 up to 20000 = 4000

 

[Hui]

skAz


Firstly, Welcome to the Chandoo.org Forums


Do you want the answer like?

5000, 10,000, 15,000, 20,000, 20,001


or


5000

10000

15000

20000

20001


or


up too 5000 = 5000

over 5000 and up too 10000 = 5000

over 10,000 up to 15000 = 5000

over 15,000 up to 20000 = 4000

 

[skAz]

Thank you!


It's the third option I'm looking for. I did finally come up with a rather lengthy formula that works, but, just seemed there may be some sort of bin/bucketing routine in excel.

 

[Hui]

skAz

I'll assume you number is in A1

A2:

=IF(A1<5000,"Up to 5000 = "&A1,"Up to 5000 = 5000")

A3: =IF(INT($A$1/5000)<(ROW()-ROW($A$2)),"","over "&(ROW()-ROW($A$2))*5000&" and up to "&(ROW()-ROW($A$2)+1)*5000&" = "&MIN(5000,$A$1-INT((ROW()-ROW($A$2))*5000)))

Copy A3 down

 

[skAz]

Interesting approach.... your definitly more the excel Ninja than I.


Thank you!