Hello,

First of all - Hi Everyone! This is my first by surely not last post and I'm glad to be able to join this community :).

Quite recently I set out to create a very simple - or so I believed at the time - Excel file that was supposed to use Solver capabilities to enable mathematically randomized sample check generation, which could be repeated and yield different result each time.

To be more specific - let's say that your goal is to check 30% of certain subjects each month, and 50% each quarter (so 30% in January and February, 50% in March etc.) and you want each subject to be equally represented in the sample check annualy - so let's say you need each subject to be checked 4 to 5 times a year. These are the conditions.

Additionally, I figured it would be best to also set the target cells to display binary numbers only - and so 0 would mean no check, and 1 would mean check in a particular month. And thus defined, the whole task is easy peasy for Solver. Or would be, if the target cells weren't limited to 200 (or some such) and I need to be able to randomize 744, as there are 62 subjects in total.

Could you please suggest another way to do this? Surely I am missing some relatively easy solution here - the concept itself seems rather basic. What do you think? Below are some further details taken from the Spreadsheet in question:

=ROUND(COUNTA(A:A)*(C1/100);0)

The number of subjects in a monthly check, where C1 is the percentage of subjects that are supposed to be checked each month - this is so to enable seemless alterations of subject number and required sample percentage.

=ROUND(COUNTA(A:A)*(G1/100);0)

The number of subjects in a quarterly check - analogically to the above, where G1 is the percentage of subjects that are supposed to be checked each quarter.

=(IF(AND(N4>=$N$2;N4<=$O$2);"OK";"NOK"))="OK"

This validates the total number of checks in a year . N4 is a sum of checks for a given subject. N2 and O2 are the minimum and maximum values. This is modifiable as well and changes depending on the required check percentage. Retrieves TRUE if valid.

=B66=$C$2

Validation of the number of subjects checked during a given month that is inserted in each subject's row, where B66 is the sum of checks for a given month and C2 is the formula described before: =ROUND(COUNTA(A:A)*(C1/100);0)

=D66=$G$2

Validation of the number of subjects checked during a given month that is inserted in each month's column, where D66 is the sum of checks for a given quarter and G2 is the formula described before: =ROUND(COUNTA(A:A)*(G1/100);0)

=IF(AND(O4:O65;B67:M67);1;0)

Validates all conditions described before - created to provide a single goal for Solver and make editing easier. A result of 1 means that all conditions were met and the sample successfully generated.

And here's the Excel file for your reference: https://onedrive.live.com/redir?resid=8BCC5C37877928DB!3302&authkey=!AL44mwehz4V5Vyc&ithint=file%2cxlsx

Please let me know if more information is required to solve (ehem) this problem. Or maybe there's a completely different way to go about this?

Also posted in MS Office Excel Forum...
 

Hi MioszNiedziela,

Based on your description and your sample file, my understanding is there are 2 conditions need to be used at the same time in your Excel file.

Condition 1, for example Colum January you only need 50% subjects to be random check and if the subject is checked then the corresponding cell in Colum January get the result is 1, if not to be checked the result is 0. But the result is random with the percentage for each month.

Condition 2, for example Row subject 1 you want the subject to be checked only 4 or 5 times one year. And the result also is random.

If my understanding is correct, you can't get the result by using formulars. I suggest you can try to use VBA code to get your result.

If my understaing is incorrect, please tell me the correct requirement. I'm glad to help and follow up your reply.

Regards,

Emi Zhang
TechNet Community Su

@Emi Zhang CHN

Hi and thank you for the reply.

I believe that with the condition 1 you're absolutely right - there's a set percentage of how many subjects should be checked monthly/quarterly and these subjects should be picked randomly.

I'm not entirely sure if I can agree with the description of the condition 2 - the number of checks for a given subject per year is nothing more than an average calculated directly from the percentages mentioned in condition 1. In other words, it guarantees that the spread of the sample check is as equal as possible for all subjects throughout a year.

Please let me know if more information is required as the explanation above may be lacking. I'd greatly appreciate some hints on the matter - and VBA could be a viable option as well, I think - only I consider myself rather a novice and therefore require guidance in that area as well, in whatever form it can be provided.

• Edited by MioszNiedziela 11 hours 35 minutes ago

@James Cone

Yes, you're absolutely right. Maybe it would be more appropriate if we continued the subject e.g. only here from this point forward?

I of course noted your reply on the MS Office Excel forum and am currently studying the Workbook you kindly provided. Thank you very much! So far - if I "get" what you did there, of course - I think the proposed solution might be a little overcomplicated - it would be theoretically possible to "fudge the numbers" :) to a point where the criteria are met, but please note that the whole idea was for the criteria to be easily modifiable - and if you change the criteria in the "Sheet Possible", the fudging would have to start all over again. Or am I missing something? What do you think?

• Edited by MioszNiedziela 11 hours 34 minutes ago

@Emi Zhang CHN

Hi and thank you for the reply.

I believe that with the condition 1 you're absolutely right - there's a set percentage of how many subjects should be checked monthly/quarterly and these subjects should be picked randomly.

I'm not entirely sure if I can agree with the description of the condition 2 - the number of checks for a given subject per year is nothing more than an average calculated directly from the percentages mentioned in condition 1. In other words, it guarantees that the spread of the sample check is as equal as possible for all subjects throughout a year.

Please let me know if more information is required as the explanation above may be lacking. I'd greatly appreciate some hints on the matter - and VBA could be a viable option as well, I think - only I consider myself rather a novice and therefore require guidance in that area as well, in whatever form it can be provided.

• Edited by MioszNiedziela Tuesday, June 30, 2015 7:54 PM

@James Cone

Yes, you're absolutely right. Maybe it would be more appropriate if we continued the subject e.g. only here from this point forward?

I of course noted your reply on the MS Office Excel forum and am currently studying the Workbook you kindly provided. Thank you very much! So far - if I "get" what you did there, of course - I think the proposed solution might be a little overcomplicated - it would be theoretically possible to "fudge the numbers" :) to a point where the criteria are met, but please note that the whole idea was for the criteria to be easily modifiable - and if you change the criteria in the "Sheet Possible", the fudging would have to start all over again. Or am I missing something? What do you think?

• Edited by MioszNiedziela Tuesday, June 30, 2015 7:54 PM

Re:  random audit generator

I have just (June 30, 2015 - 5:00pm) posted a revised copy of the Sample Generator workbook (revison R2) that uses some vba.
I will leave it on the Jumpshare website for just ~ a day.

Download from... http://jmp.sh/K95N3ee

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
http://jmp.sh/K95N3ee

Hi MioszNiedziela,

James Cone shared a sample file in his reply please check if it works for you.

If you decide to use VBA to get your result and want to know more infromation about VBA. I suggest you describe your problem more simply or upload a screenshot and post your issue to MSDN forum for Excel Developer:

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev&filter=alllanguages

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Su

@James Cone

Wow! It looks quite close to the desired results. I even managed to modify it so the annual audits for each subject fall between 4 and 5 - which is great. What I can't seem to get right, though. is the monthly and quarterly percentages... any suggestions?

Hi Emi Zhang CHN,

Thank you very much for the suggestion - I certainly appreciate a little guidance since the community's structure is new to me.

Re:  random audit generator

@MiloszNiedziela
You are not getting exactly 30% monthly or 50% quarterly because the numbers generated are kinda random.
Using a "random" number system to create the numbers you want sounds like vote counting in Russia.

I have a free excel 'Special Randoms' add-in that has an option to generate random numbers that average a specified amount.
If I can find a Moscow sales rep, I should be able to retire. <joke>

'---
Jim Cone

@James Cone Many thanks for your reply and sorry for the delay in my own. I believe that Excel Data Analysis has similar capabilities - or maybe I'm mistaken. In any case I still don't think it would be quite possible to "fudge" it just right with the above - or otherwise it would be quite time consuming. I don't know, it's just one of these situations when I don't really know the answer, but I'm positive that it has to be a really simple one - I mean, this is basic mathematics, isn't it? Yeah but still even if it is, my B is not getting me very far! (should've studied harder) Just to maybe get some fresh ideas (and to look at this from a new angle myself) I decided to follow Emi Zhang's advice and posted in on the Dev forum here: https://social.msdn.microsoft.com/Forums/office/en-US/1d8a82c8-c0a2-40bf-ab89-e583a29ee557/randomized-audit-with-two-constants?forum=exceldev Perhaps you will find something new in the way I described it this time around?