Tricky random extract

[Tom.Jones]

Hi,

In a table, with 500 rows and 20 columns, of documents that cost 200,000 dollars. How do I extract 25% of these costs to physically check the documents (sort of sampling but random).

Thank you.

 

[Tom.Jones]

In column 20 is costs. I need to extract 25% from $ 200,000 (this mean $ 50,000).
Sum of costs of extras documents must be around $ 50,000.

 

[Tom.Jones]

anybody

 

[Tom.Jones]

????

 

[Tom.Jones]

Hey!!!!

All guru from here are in holiday?? )

Or is to difficult??

 

[Eric W]

Lot of ways to do this. You could add a work column with random numbers in it, then filter by the right value. For example:

	A	B	C	D
1	Item	Value	rand	Rand
2	1	35	0.095489	>=0.570958207034433
3	2	74	0.359186
4	3	86	0.78392
5	4	79	0.806415
6	5	94	0.143845
7	6	84	0.597873
8	7	13	0.051086
9	8	19	0.943706
10	9	34	0.186608
11	10	13	0.375809
12	11	83	0.094656
13	12	19	0.149419
14	13	4	0.750347
15	14	32	0.384563
16	15	52	0.697151
17	16	64	0.131489
18	17	47	0.04358
19	18	41	0.821631
20	19	23	0.734566
21	20	16	0.044384

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula C2 =RAND() D2 =">="&LARGE(C:C,15) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in C2, then copy down. Put the formula in D2, and change the 15 to the number of samples you want. Then select column C, go to the Data tab, Advanced Filtering > Filter in place > and give it a criteria range of D1:D2.


Or you could add the Analysis pack (File > Options > Add-ins > Go (next to Excel Add-ins) > and check the Analysis ToolPak box. Now go to the Data tab > Data Analysis > Sampling > and follow the prompts. Be aware that this tool sometimes will select the record more than once.


Or you could write a VBA macro to do this, but we'd need more information on how your data is laid out.

 

[Tom.Jones]

Thanks for the reply Eric,

I guess I did not explain it well.
In your example, the sum of the numbers in column B is 912.
25% of 912 is 228.
Now I have to find those items in column A whose sum (from column B) is close to 228.

Let say for sum of 228 we have:

Item.......Value

2...........12

5...........8
.....
.....

...........228

We need to list all items and corresponding amount (if their sum is near 228)

Thank you.

 

[Eric W]

What do you consider "close"? Near to 1/4 the total, based on percent, value, standard deviation? How many times to you want to keep looking in search of a closer set?

You can enhance the above example like this:

	A	B	C	D	E	F	G	H	I
1	Item	Value	rand	Rand		Sum	25% of sum	Sample Sum
2	1	35	0.421379	>=0.825376116024242		912	228	226
3	2	74	0.594056
4	3	86	0.310353
5	4	79	0.825376
6	5	94	0.611129
7	6	84	0.983807
8	7	13	0.899614
9	8	19	0.159377
10	9	34	0.944097
11	10	13	0.382167
12	11	83	0.54371
13	12	19	0.712575
14	13	4	0.345105
15	14	32	0.133184
16	15	52	0.498221
17	16	64	0.081112
18	17	47	0.429398
19	18	41	0.407838
20	19	23	0.482752
21	20	16	0.869885

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =SUM(B:B) G2 H2 =SUMIF(C:C,">="&LARGE($C$2:$C$21,5),B:B) C2 =RAND() D2 =">="&LARGE(C:C,5) <tbody> </tbody>

<tbody>
</tbody>

G2: =F2/4

In this case, you have the additional formulas in F2:H2. Keep pressing F9 (recalculate) until H2 is "close" enough for you. Then you can run the Advanced Filter to show the specific items.


If this doesn't work for you, please specify what "close" means to you. Where is the original data, how many rows and columns in your table. How do you want the results shown? Filtered, highlighted, extract to another sheet? Since you have 2 criteria (count + amount), requiring some iteration, formulas might be tricky. The above method might be your best bet if you want formulas. Otherwise, I could devise a method using the Solver, or write a VBA macro.

 

[Tom.Jones]

Eric,


I can not list items from column A, the sum of which is close to the value in H2.
Can you gave me, a formula, to list - say - in column K, items from column A , which correspond to the numbers in column B whose sum is close to the number in H2.

In column K to be items and in column L to have that numbers.

Thank you.

 

[Eric W]

Like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item	Value	rand	Rand		Sum	25% of sum	Sample Sum		Index	Item	Value
2	a	35	0.539431	>=0.779121250994762		912	228	233		3	c	86
3	b	74	0.448912							6	f	84
4	c	86	0.819341							7	g	13
5	d	79	0.633789							9	i	34
6	e	94	0.503725							20	t	16
7	f	84	0.974036
8	g	13	0.850117
9	h	19	0.567506
10	i	34	0.796396
11	j	13	0.637188
12	k	83	0.342703
13	l	19	0.535796
14	m	4	0.190634
15	n	32	0.448716
16	o	52	0.416354
17	p	64	0.339006
18	q	47	0.298755
19	r	41	0.680239
20	s	23	0.134915
21	t	16	0.779121

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula K2 =INDEX($A$2:$A$21,J2) L2 =INDEX($B$2:$B$21,J2) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula J2 {=SMALL(IF($C$2:$C$21>=LARGE($C$2:$C$21,5),ROW($C$2:$C$21)-ROW($C$2)+1),ROWS($J$2:$J2))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You may want to copy the K:L columns and Paste as Value somewhere else, otherwise the next calculation will change them.