Help fix formula to Get random sample from DATA in a column D from a list

Originally Posted by kvsrinivasamurthy

In G2.

Formula:

=LET(a,SEQUENCE(INT(COUNTA(A:A)/24)+1),b,IFERROR(INDEX(D:D,IF(a=1,RANDBETWEEN(1,17),RANDBETWEEN(18+(a-2)*24,41+(a-2)*24))),""),HSTACK(a,b))

Hi, thanks for the formula, i checked it and it works partially, Sometimes it takes 2 random numbers from the same day, I modified the data to clearly see the issue and the numbers in Column I marked in red/yellow are mistaken. I added a conditional formatting to see it.
So there is an issue with the formula but i cant fix it. Also for some reason, the sometimes when the formula is used, the first DATA takes the title of the column.
In columns J and K, I added the lowest and highest value that the random data should give and Column I gives the difference between them, if the result is above 0 or lower than -24, the data is not correct and something is wrong with the formula.
THanks for the help, waiting for the reply.

Excel Help ans MODIFIED.xlsx

Originally Posted by kvsrinivasamurthy

In G2.

Formula:

=LET(a,SEQUENCE(INT(COUNTA(A:A)/24)+1),b,IFERROR(INDEX(D:D,IF(a=1,RANDBETWEEN(1,17),RANDBETWEEN(18+(a-2)*24,41+(a-2)*24))),""),HSTACK(a,b))

HI, I tried using this formula, it works perfectly BUT.... i cannot find how can i expand it to the end and make it work properly... if i expand it, the cells selection in the formula only advances 1 number From D2:D25 to D3:D26 for example...

=INDEX(D2:D25;RANDBETWEEN(1;ROWS(D2:D25))) I have this formula for the random data for the first 24 items. What this does is select randomly a data from the selection of 24 cells.

=INDEX(D26:D49;RANDBETWEEN(1;ROWS(D26:D49))) And this one for the next 24 cells and so on....

THe next one would be...

=INDEX(D50:D73;RANDBETWEEN(1;ROWS(D50:D73)))... I must advance the formula 24 cells every time. Until i hit 10000....

How do i fix the formula so that i can do this over 400 times and make it automatic? I tried to see if i can apply math to the sell selection but i cannot think how. I cannot write the formula manually in each cell... Best would be for the formula to advance 24 cells on its own rather than 1 cell at a time when i expand it

In cell F1, you can use the following formula:
=IF(A1=1, INDEX(D1:D16, RANDBETWEEN(1, 16)), INDEX(OFFSET(D2, (A1-2)*24, 0, 24, 1), RANDBETWEEN(1, 24)))

This formula checks if it's Day 1, and if so, it selects a random entry from D1 to D16. For the other days, it uses the OFFSET function to shift the range by the appropriate number of rows and selects a random entry from the 24 available.

Originally Posted by beecerealcello

In cell F1, you can use the following formula:
=IF(A1=1, INDEX(D1:D16, RANDBETWEEN(1, 16)), INDEX(OFFSET(D2, (A1-2)*24, 0, 24, 1), RANDBETWEEN(1, 24)))

This formula checks if it's Day 1, and if so, it selects a random entry from D1 to D16. For the other days, it uses the OFFSET function to shift the range by the appropriate number of rows and selects a random entry from the 24 available.

i see... using an if... What i did while i was waiting was use =INDEX(B 2 :B 17 ;RANDBETWEEN(1;ROWS(B 2 :B 17 ))) in excel, and i pasted each word in a separate cell as. then i copied it down until the end. After that i just had to make the numbers that represent the cells be correct. I used Previous Number+24 and finally i copied it down to notedpad, remove any unwanted spaces and paste it down in excel. It worked perfectly... Problem is that after doing this... i found out that some days have 23 entries, some have 25 entries, which i find it extremely odd. If it has less... well, one hour was not registered.. but to have 25? means that somehow it did not follow the rules to register data every 60 min.... THen i thought, how do i fix it... i can use =A2-DATE(YEAR(A2);1;0) instead of =day to get the day of the year and use that as a condition somehow.... How would you modify the formula? I must point out that the data is not numbers from 1 to 90000 or somethign. each day has numbers like 83853,139493277499 and the like.... Is there a function that allows me to count how many entries are per day? This may help out cause the data entries are inconsistent. Some days have 23, others 24 and others 25.... So if i can count how many of those are and apply that number to the rows that need to be selected it may do the job

Thanks, i have solved the issue. Since i couldnt use a single formula, i used 4. What i did was obtain the number of the year that each date represented. Counted each of those and obtained the number. Then i wrote the formula by parts in excel leaving the cell number alone, and then i pasted there the result of each count of the days. Then i copied the whole formula in notepad, removed the extra spaces and i pasted it in excel. It game me something like this.
=INDEX(B 2 :B 17 ;RANDBETWEEN(1;ROWS(B 2 :B 17 )))
=INDEX(B 18 :B 41 ;RANDBETWEEN(1;ROWS(B 18 :B 41 )))
=INDEX(B 42 :B 65 ;RANDBETWEEN(1;ROWS(B 42 :B 65 )))
=INDEX(B 66 :B 89 ;RANDBETWEEN(1;ROWS(B 66 :B 89 )))
=INDEX(B 90 :B 113 ;RANDBETWEEN(1;ROWS(B 90 :B 113 )))
=INDEX(B 114 :B 137 ;RANDBETWEEN(1;ROWS(B 114 :B 137 )))
=INDEX(B 138 :B 161 ;RANDBETWEEN(1;ROWS(B 138 :B 161 )))
=INDEX(B 162 :B 185 ;RANDBETWEEN(1;ROWS(B 162 :B 185 )))
=INDEX(B 186 :B 209 ;RANDBETWEEN(1;ROWS(B 186 :B 209 )))
=INDEX(B 210 :B 233 ;RANDBETWEEN(1;ROWS(B 210 :B 233 )))
=INDEX(B 234 :B 257 ;RANDBETWEEN(1;ROWS(B 234 :B 257 )))
=INDEX(B 258 :B 281 ;RANDBETWEEN(1;ROWS(B 258 :B 281 )))
=INDEX(B 282 :B 305 ;RANDBETWEEN(1;ROWS(B 282 :B 305 )))
I posted this and i solved the problem. No longer the data is inconrrect and it used the appropiate number of entries per day even if they are different.