Take a Value range and evenly distribute into 4 groups

[Doug Mutzig]

Hi all,

I am trying to work out how to automate the calculation of percentage increases based on an initial percentage range.

For example, if you were given a range of 3 to 9 % how do you take this range a split it into 4 groups with a high and low value in each? The first group's high would be the highest value from the range and the last (4th) group's low would be the lowest value from the range with the rest of the values being an even distribution.

I have tried a count of integers between the initial values, then adding 2 to account for the values in the range, and using the Sequence formula to generate a list of the values but I am stuck on if this is even the right way to go and how to proceed from there.

 

[KRice]

If I'm understanding correctly, you could do something like this:

Book1
	A	B	C	D	E	F
1	Given Range (%)		O	E	M	A
2	Low	3.0%	7.5%	6.0%	4.5%	3.0%
3	High	9.0%	9.0%	7.5%	6.0%	4.5%
Sheet1

Cell Formulas
Range	Formula
C2:F2	C2	=SEQUENCE(1,COUNTA($C$1:$F$1),$B$3-($B$3-$B$2)/COUNTA($C$1:$F$1),-($B$3-$B$2)/COUNTA($C$1:$F$1))
C3:F3	C3	=SEQUENCE(1,COUNTA($C$1:$F$1),$B$3,-($B$3-$B$2)/COUNTA($C$1:$F$1))
Dynamic array formulas.

 

[Doug Mutzig]

Hi KRice!

This works perfectly! Thank you very much as I had not thought of an array when looking at this.

 

[KRice]

Glad to help. You could shorten the formulas somewhat by using the LET function to help with the redundant terms:

MrExcel_20220430.xlsx
	A	B	C	D	E	F
5	Given Range (%)		O	E	M	A
6	Low	1.0%	10.8%	7.5%	4.3%	1.0%
7	High	14.0%	14.0%	10.8%	7.5%	4.3%
Doug Mutzig

Cell Formulas
Range	Formula
C6:F6	C6	=LET(a,COUNTA($C$5:$F$5),SEQUENCE(1,a,$B$7+($B$6-$B$7)/a,($B$6-$B$7)/a))
C7:F7	C7	=LET(a,COUNTA($C$5:$F$5),SEQUENCE(1,a,$B$7,($B$6-$B$7)/a))
Dynamic array formulas.