I am looking at NPS scores for a dataset where the population frequently changes, so I have decided I will use three different calculations for optimal bin sizes (Sturgis, Scott and Freedman and Diaconis). Depending on the dataset population, I will output one of the bin size recommendations in a separate cell.
From this value, I want to expand or contract my bin range, which will act as my primary X-axis on a bar chart. For this reason, it needs to be dynamic so I can name the range.
If the recommended bin size is 10, then I need to output:
-100
-90
-80
-70
-60
-50
-40
-30
-20
-10
0
10
20
30
40
50
60
70
80
90
100
110
120
130
140
150
160
170
180
190
200
..from which I then can check the frequency of my population of data. And if the recommended bin size is 5, then the same but in half those increments.
Any suggestions on how to go about this, based on the number of one cell? I need it to start at -100 and stop at 100..
Solved with help by BigBen in the comments. =SEQUENCE() solved it. In my example, specifically:
=SEQUENCE((ROUNDUP(200/AP28,0)),1,-100,AP28)
Where I split the range (-100 to 100 which is 200) by the recommended bin size number, then specified the bin size (cell AP28) and where in the range I wanted to start (-100)