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. 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. 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 as there are >> 2-3 conditions on basis of which sampling should be done. >> It should be done for 4 calls per month per type (column A for 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.
Copy of random_sample.xlsx
Description: MS-Excel 2007 spreadsheet
