ranges with dynamic lengths

[beng986]

Hi,

I have two questions both around creating a range with a dynamic length.

In cell A2 I have a input column with the number 6 in it. This represents 6 people.

In A4 I have =sequence(A2,1,1,1), giving me a list of 1 to 6. In B4 I have, =MROUND(RANDARRAY(A2,1,5000,100000,TRUE),5000). This gives me each persons theoretical salary.

However in C4 I want to give them a label of 30,60,90,120, how would I do this, whilst keeping the sheet dynamic? In the same way I want to give them a text label and keep it dynamic so that if the number in A2 was changed all the ranges would as well.

thanks

 

[Fluff]

You haven't said how you would allocate 30,60,90,120, but maybe

+Fluff v2.xlsm
	A	B	C	D
1
2	6
3
4	1	50000	90	90
5	2	60000	90	90
6	3	40000	60	60
7	4	90000	120	120
8	5	20000	30	30
9	6	80000	120	120
Main

Cell Formulas
Range	Formula
A4:A9	A4	=SEQUENCE(A2,1,1,1)
B4:B9	B4	=MROUND(RANDARRAY(A2,1,5000,100000,TRUE),5000)
C4:C9	C4	=IF(B4#<25000,30,IF(B4#<50000,60,IF(B4#<75000,90,120)))
D4:D9	D4	=LOOKUP(B4#,{0,25000,50000,75000},{30,60,90,120})
Dynamic array formulas.

 

[beng986]

This is just about creating some random data. It would be amazing to weight the 30,60,90,120,180 to 50%,30%,10%,5%,5%. is that possible?

 

[Fluff]

I've no idea, as I don't understand what you are trying to do.
What are the rules for deciding which value a cell gets?

 

[beng986]

I am trying to create some quasi random data. In this case there are 6 people to create data for.

A4 is the numbers 1 to 6 representing each person
B4 is the salary of each person rounded to the nearest 5,000
D4 would be an ID, eg EMP001, EMP002
E4 would be how often they are paid e.g every 30 days, 60 days etc. Of course most people are paid every 30 days so I am trying to weight it to the 30 days

 

[Fluff]

For col D use

="EMP"&TEXT(A4#,"000")

Please explain exactly what you want for col E.

 

[beng986]

Thanks. Haven't seen the use of # before. Really useful.

Invoice Number	Employee name	Amount	Payment period
1	EMP001	80,000	60
2	EMP002	90,000	90
3	EMP003	45,000	180
4	EMP004	30,000	180
5	EMP005	10,000	30
6	EMP006	25,000	120

I am looking to create the above. I am currently using =CHOOSE(RANDBETWEEN(1,5),30,60,90,120,180) to generate the payment period. Firstly I would like to make this dynamic as you have done above.

I would also like to, if possible, weight the outcomes to the below

30	50%
60	25%
90	15%
120	5%
180	5%

 

[Fluff]

You could replace your formula with

=CHOOSE(RANDARRAY(A2,,1,5,1),30,60,90,120,180)

or to weight it

=LOOKUP(RANDARRAY(A2,,1,100,1),{0,50,75,90,95},{30,60,90,120,180})

 

[beng986]

thanks Fluff really helpful

 

[Fluff]

You're welcome & thanks for the feedback.