Pick random numbers within the range except blanks

[angelosison00]

can you please help me with the right formula in picking random cell in a selected range except blanks cells?
i need to pick atleast 5 random unique cells.

[AliGW]

Do you want to return their values? If so, in what format?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[angelosison00]

please see my sample list.

[AliGW]

That does not really help, I am afraid. You said this:

picking random cell in a selected range except blanks cells

and showed this:

Excel 2016 (Windows) 32 bit

	A	B
1		Range
2
3	Pick1	0.250377
4	Pick2	0.716814
5	Pick3
6	Pick4	0.776088
7	Pick5	0.79441
8	Pick6	0.652136
9	Pick7	0.074578
10	Pick8	0.066637
11	Pick9	0.183517
12	Pick10	0.487468
13		0.310347
14
15		0.178392
16		0.889321
17		0.629138
18		0.302346
19		0.49476
20		0.607239
21		0.29172
22		0.225574
23
24		0.891602

Sheet: Sheet1

Try a bit harder, please, to clarify your requirement.

[angelosison00]

i hope this will help, i suppose to pick random cell in sheet1 except blanks and so on for the other picks. it should be unique every picks.

[AliGW]

No, sorry - I have no idea what you are trying to do, and now you have introduced three sheets.

Try to EXPLAIN in WORDS what the process is that you have in your mind - step-by-step.

[angelosison00]

A3 - i need to pick random cell within the list from Sheet1 except for blanks.
A4 - same with a3, but it should be unique with a3.
A5 - same with a3, but it should be unique with a3 and A4.
i cannot remove the blanks in my list for some reason.

[AliGW]

I hope somebody else can work out what you want. I am out of time today - sorry.

[shukla.ankur281190]

Try

B3

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.