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?