Hi,
I Have a row of numbers with blanks in & i need to create a random number between 7 & 18, but i cant have a blank or a zero, it must only be one of the numbers in the row.
7 8 9 10 11 13 14 15 17 18
Obviously =RANDBETWEEN(7,18) wont work & i have tried Macro's but they seem to crash Excel
If you can help that would be great.
If you set up your data in the first column, try:
Please Login or Register to view this content.
Hi, thanks but i cant really get this to work, it only puts 1 number in cell "A1" & i need the random numbers to be un a Row elsewhere in the sheet, not at the beginning?
If your list of allowed numbers in A1:A10, then INDEX($A$1:$A$10,RANDBETWEEN(1,10)) should return a random value from the range.
Originally Posted by shg
If you numbers are in B2:Z2 tryFormula:
Please Login or Register to view this content.
Please try
=Choose(RandBetween(1,10), 7, 8, 9, 10, 11, 13, 14, 15, 17, 18)
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
thanks for the reply but as there are blanks between the numbers in the row, it still brings up blanks
thanks but this still shows blanks where there are spaces between the numers
No idea who you are talking to, but did you try my suggestion, it ignores the blank cells.
thanks however as the numbers change quite often & as there are many rows it would be too time consuming to keep updating all the formulars all the time
Hi Fluff, yes i tried your formula but it it still brings up a blank sometimes
Hi Fluff, you are right, however the rows of numbers i have are generated by formulas or links to other cells, so your formula shows up #NUM, when there is a blank in a cell, however if i type the numbers in manually (which is impractical) then your formula works
We might need a sample file to see exactly what kind of set up you have and how the blanks are figuring into this and all.
As a guess, this worked:
1) If my numbers intermingled with blanks are in A1:Z1.
2) Will there always be 10 real numbers to choose from, or do you need to determine how many numbers there are at run time (a COUNT() function should work, if at run time). For now, I am assuming there will always be 10. Replace with something else if necessary.
3) Add a helper row below that assigns an integer to each number. Something like =IF(A1="","",1) in A2. In B2, =IF(B1="","",MAX($A2:A2)+1) copy/paste/fill into C2:Z2
4) Extract a random number:
4a) RANDBETWEEN() to get a random integer between 1 and 10 (or COUNT(A1:Z1)) RANDBETWEEN(1,10)
4b) a MATCH() function to find that integer in row 2 MATCH(RANDBETWEEN(...),A2:Z2,0)
4c) an INDEX() function to extract the corresponding number from row 1: =INDEX(A1:Z1,MATCH(...))
Are we getting closer?
If you won't always have 13 numbers in the row, tryFormula:Please Login or Register to view this content.
Last edited by Fluff13; 01-27-2022 at 09:09 AM.
Hi Fluff, i think that i can close this out with your solution, thanks again
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks