Breakdown a given number by ranges

[TexasWonder]

I have an issue I can't solve, if given a variable number, I'm trying to breakdown that number based on a set of fixed ranges. For example, the number 1200 fits into the fixed ranges below like so:

Range:. Breakdown of "1200"
1-200. 200
201-400. 199
401-600. 199
801-infinity. 602

I can't quite figure out what formula would make this count happen

 

[awoohaw]

Can this work? (I am assuming your the skip from 600 to 800 is a typo).

Mr Excel Questions2.xlsm
	A	B
1	Number:	1200
2	Nbr Ranges:	4
3	Initial Intervals	200
4	Range Start	Range End
5	1	200
6	201	400
7	401	600
8	601	1200
Sheet14

Cell Formulas
Range	Formula
B5	B5	=B3
A6:A8	A6	=IF(ROWS($A$5:A6)>$B$2,"",1+B5)
B6:B8	B6	=IF(ROWS($A$5:A6)>$B$2,"",IF(ROWS($A$5:A6)=$B$2,$B$1,A6+$B$3-1))

 

[awoohaw]

I failed to put the 3rd column in (the counts). Yours is incorrect as you are subtracting the start of the range. You need to subtract the end of the prevous range. So your counts are 200,200,200,600.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your ranges look strange to me. Apart from the calculation issue already mentioned, your ranges do not include 601 to 800.

Anyway see if this is something that you can adapt.

23 02 06.xlsm
	A	B	C
1	Number	1200
2
3	Range Start	Range End	Result
4	1	200	200
5	201	400	200
6	401	800	400
7	801	infinity	400
Ranges

Cell Formulas
Range	Formula
C4:C7	C4	=LET(r,B$1-SUM(C$3:C3),IF(r=0,"",MIN(r,IFERROR(B4-A4+1,r))))

23 02 06.xlsm
	A	B	C
1	Number	275
2
3	Range Start	Range End	Result
4	1	200	200
5	201	400	75
6	401	800
7	801	infinity
Ranges

Cell Formulas
Range	Formula
C4:C7	C4	=LET(r,B$1-SUM(C$3:C3),IF(r=0,"",MIN(r,IFERROR(B4-A4+1,r))))

 

[TexasWonder]

Thanks everyone! This is very helpful. Yes typing on my phone rather quickly so initially my post had the wrong range calculation and I did accidentally forget to put 601 to 800.

 

[awoohaw]

If one of the posts provides you with the answer please mark it as the answer.

 

[awoohaw]

Update to Post #2.

If you want to display "Infinity" instead of the max amount as input, you can use this formula in column B:
=IF(ROWS($A$5:A6)>$B$2,"",IF(ROWS($A$5:A6)=$B$2,"Infinity"A6+$B$3-1))

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.