Take a Value range and evenly distribute into 4 groups

Doug Mutzig

Board Regular
Joined
Jan 1, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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. 2022-04-29_14-35-21.jpg

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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
If I'm understanding correctly, you could do something like this:
Book1
ABCDEF
1Given Range (%)OEMA
2Low3.0%7.5%6.0%4.5%3.0%
3High9.0%9.0%7.5%6.0%4.5%
Sheet1
Cell Formulas
RangeFormula
C2:F2C2=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:F3C3=SEQUENCE(1,COUNTA($C$1:$F$1),$B$3,-($B$3-$B$2)/COUNTA($C$1:$F$1))
Dynamic array formulas.
 
Upvote 0
Solution
Hi KRice!

This works perfectly! Thank you very much as I had not thought of an array when looking at this.
 
Upvote 0
Glad to help. You could shorten the formulas somewhat by using the LET function to help with the redundant terms:
MrExcel_20220430.xlsx
ABCDEF
5Given Range (%)OEMA
6Low1.0%10.8%7.5%4.3%1.0%
7High14.0%14.0%10.8%7.5%4.3%
Doug Mutzig
Cell Formulas
RangeFormula
C6:F6C6=LET(a,COUNTA($C$5:$F$5),SEQUENCE(1,a,$B$7+($B$6-$B$7)/a,($B$6-$B$7)/a))
C7:F7C7=LET(a,COUNTA($C$5:$F$5),SEQUENCE(1,a,$B$7,($B$6-$B$7)/a))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top