See below:Paul-----------------------------------------
On Friday, January 29, 2016 3:19 AM, SG <sonal...@gmail.com> wrote:
Hi Paul, I got the sheet. Thankyou so much for all your effort in this
sampling. I'm really thankful.
Only one thing i observed is that, in raw data , there are 4 entries of Rohit
in Jan but in Random Sampling section, only two are coming as sample for Rohit
whereas it should be 4calls,per person, per month with available slots as per
the slab. PFA for you.I don't think that is a reasonable expectation.In
January, rohit may appear 4 times, but it only appears TWICE in the 0-10 minute
category, ONCE in the 10-20 minute and ONCE in the 20-30 minute.
In the sample data, there are (5) records in the 0-10 minute category for
January.With only (4) people, one of the (5) records will not be selected.When
the data is sorted by the Rand" column, there is a "random" chance that only
one of the rohit records will be selected.
Also, Rand function don't have any impact in selection of samples. Am i
right?Please check if this can be solved as this is the condition for
sampling.In order for the RAND() function to affect the sampling, you must sort
the data.When you sort it by the Rand column, the "index" number gets
recalculated based on the "position".so, each time you sort the data, you'll
get a different set of selections for the (4) people.
Attached you will find a version of the file with a "sort" button.
On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote:
No Paul, I didn't get it. Can you please share it again.
On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote:
Yeesss.. and responded with an update.Did you not receive it?
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Thursday, January 28, 2016 12:43 AM, SG <sona...@gmail.com> wrote:
Paul, have you checked it?
On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote:
Thanks for your quick response Paul. The line ""In case, any slot not
available, it should pick so on" means that in case any slot is not available,
it should skip it & choose the call from next slab. Your trick is helping me
very much. One more modification i need is per person sampling. That means, per
person, 4 calls/month for all 4 slabs.Please try one more time.
On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner wrote:
Here's a possible solution.Basically, you need to determine which "set" the
record falls into.I used:
IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0))))this says that if the
Duration value is >0 and <= 10 minutes, then it belongs in set #1.>10 and <= 20
is set 2, >20 and <= 30 is set 3>30 and <= 60 is set 4 (you could make it >30,
but it "looks cleaner" to keep them all the same!)
IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
IF(G2>TIME(0,30,0),4,0))))doesn't look quite so "nice" in my opinion! (just a
LITTLE OCD)
Next, I wanted to use the month in determining the set, so I prefixed the
formula with:=MONTH(F2) &"."&
giving me:=MONTH(F2) &"."&
IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0))))
I added two columns (A and B).Put this in A2 and copied it down the list.
Next, in column B I used =RAND() and copied IT down the list.(technically, this
could have been put at the end)
Next, sort these records by the RAND column.
Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 etc. in
the first column.I then used vLookup to find the first record in the data that
matches this "set".Note: since I used =month() & "." & to come up with the set
names, this is TEXT, not a number.So, in the vlookup, you must make sure the
"set" you're looking for is TEXT.That is: when you enter the set numbers 1.1,
1.2, 1.3, 1.4 etc. (in column "L" in my case)Excel enters them as numeric.so in
the VLookup(), I converted them to text with TEXT().Like:
=VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE)
Now, in your data, there were no records for February that had a duration of
20-30 minutes.So no set # 2.3
I'm not sure what you want to do in this case.In your explanation, you said:"In
case, any slot not available, it should pick so on."
I have no idea what you mean by "it should pick so on".
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Wednesday, January 27, 2016 7:25 AM, SG <sona...@gmail.com> wrote:
Hi Experts
I need your help in choosing the samples for quality check.The only idea in my
mind is the Random function but this wouldn’t suffice asthere are 2-3
conditions on basis of which sampling should be done. It should be done for 4
calls per month per type (column Afor type). Different Duration slabs like out
of 4 calls for each type, One call with Duration of 1 to 10 minutes One call
with Duration of 10 to 20 minutes One call with Duration of 20 to 30 minutes
One call with Duration of 30 to 40 minutes In case, any slot not available, it
should pick so on. I really know it’s very tricky but need your help. PFA data
for your reference. Thanks in advance --
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros...@googlegroups. com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros...@googlegroups. com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
random_sample.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
