Monetary Unit Sampling (MUS) Coding help needed

Hi guys,

I have quite a tricky task to try and complete for my project. It is in relation to MUS.

I've got a very handy article that explains how MUS chooses a sample - https://cplusglobal.wordpress.com/20...ple-selection/

It's just creating a code/formula that can replicate this.

Ideally, I'm looking for something that:

1) Uses the sample size number (I've already worked out how to achieve this)
2) When you press a button/activate a macro, generates your sample size from a list of transactions based on the requirements of MUS

I apologise if this does not make much sense, but the link I provided above explains the process of what I'd like to achieve much clearer and accurate.

Overall MUS can be quite taxing, however overall the code would need to replicate the features of MUS mentioned in the article to achieve the sample size (this is not just picking random numbers, as MUS uses a protocol of procedures also all mentioned in the article)

I really hope I can have some assistance on this, and I would very much appreciate anyones help on this one.

All the best,

Nathan

Nathan, reading the article in the link and looking at the first screenshot in there, I guess you want to re-create that spreadsheet? I see the risk of circular calculation if you try to solve this with formula's. A bit of VBA is most likely a lot easier. Example attached

Originally Posted by RdwJ

Nathan, reading the article in the link and looking at the first screenshot in there, I guess you want to re-create that spreadsheet? I see the risk of circular calculation if you try to solve this with formula's. A bit of VBA is most likely a lot easier. Example attached

Hi RdwJ,

Firstly, I would like to say I very much appreciate your assistance with this issue. The spreadsheet you've attached works just as the article screenshot shows.

I used the IDEA Software to calculate the sample size and generate a sample and compared the results of it against the spreadsheet you provided and they were very similar which is indeed pleasing.

The only issue outstanding which I think would clear up any differences would be how to calculate a random start?

On the previous page - https://cplusglobal.wordpress.com/20...-sampling-mus/

It does talk of how to pick a random start from systematic selection, however I am unsure how to make this a uniform process (ie: work on different lengths of data)

If you could help me on this (you can see their random start, process etc on the link provided and the one earlier provided) then this would be most helpful.

Thank you again for what you've already done, I do really appreciate it.

All the best,

Nathan

After some research, I think I understand the principle behind a random start. This Wikipedia gave quite a good explanation - https://cplusglobal.wordpress.com/20...-sampling-mus/

Is it fundamentally generating a completely random number between 1 and whatever the sampling interval is?

Ie: My sample size is 5
- My total population value (sum of the data being tested) is 100

Therefore the sampling interval is (100/5) 20.

So, the random start can be any number between 1 and 20

If this is correct, I would just need a piece of code that would generate a random start (pick a random number) between what the sampling interval is and 1?

Reading from the link "The random start would be a value between $1 and the sampling interval."
If you use the formula =RANDBETWEEN(0,Sampling_Interval) then every time the spreadsheet is calculated (or if you press <F9> a new Random Start will be generated. Note that "Sampling_Interval" is cell E3.

Back to MUS, what I read is that indeed the sampling interval is cumulative monitory value devided by sample size, with "Random start" a random number between 0 and the sampling value.

and note that the macro does not clear existing entries, i.e. you need to remove the old answers before running the macro again....