Four random numbers with a total of 100

I have a small code (not sure if it is the correct and efficient way of doing it). It gives me four random numbers, addng all four gives me a total of 100. I want to put another condition to it. Lets say cell F1 hold "A". In this case, 8 out of 10 times the variable 'a' should hold the max value among the other 3 variables 'b, c and d'.

That means if I run the macro for the first time with cell F1 as 'A' then variable 'a' would have the max value out of the 4. If I run it second time, it may not get max value, third time also lets assume it may not have the max value but then out of the remaning 7 runs, it should compulsorily have max value because 'a' variable should get max value 8 out of 10 times.

Similarly, if F1 value is 'B", then b variable should get max value 8 out of 10 times. I am not getting a better logic to do this. Can someone help please....

Please Login or Register  to view this content.

You could be looping for a very long time until those 4 numbers sum to 100. Maybe you could do it like this:

Please Login or Register  to view this content.

Then, instead of allocating a to A1, you can get the maximum of the 4:

Please Login or Register  to view this content.

and similarly for the other cells, so they are always in order.

Hope this helps.

Pete

1. wow! loved the way you did that without a loop
2. i may have not explained my other question. I want all four numbers to be displayed in A1,B1,C1,D1. Before even the four values are assigned, I need to do one more check.
If F1 value is A, the first variable 'a' should have the max value among the other 3 Else loop until A gets the max value. Similarly,
If F1 value is C, the third variable, i.e., 'c' should have the max value
I want the above criteria to work 8 out of 10 times meaning - when the macro is run for the first time, put a value in say cell G1, next time when the macro is run, the cell G1 value will increment to 2 and looking at the G1 value, the above condition should be checked (8 out of 10 time)
I dont know if I am explaining it properly. Do let me know if any clarification is needed

No, I don't really know what you mean. You can generate an 80% distribution by using RandBetween(1,5) and then checking that to see if it is 4 or less, but I'm not sure what you want to do beyond that, or indeed, where F1 and G1 come into it.

Pete

May be I can get the explanation right this time. Lemme try again.
If you see the output of the code below, you will understand that 4 numbers are displayed in the range A1:D1 and the MAX of the four numbers would depend on what the value of F1 is. If F1 value is C, the third number would ALWAYS be MAX. Now the only one thing what I need here is.... I want to change the 'ALWAYS' to 80% right. The below code is working the way I want however, it should work as per the logic '80% of the times'

It is like rolling the dice 10 times and getting 6 eight times out of ten... Is it making sense?

PS: My code is little lengthy and may not be the correct way to do... but this is the best I could have done myself

Please Login or Register  to view this content.

So, F1 contains a letter which represents a column, and you want the maximum of the 4 values to go into that column for 80% of the time.

But, you've not really explained what you want to happen for the other 20% of the time, nor what happens to the other values - do you just want to swap them over? I.E. if b is the max value, but F1 contains "C" AND if it is within the 80% distribution, do you want b to appear in C1 and c to appear in B1, with a and d allocated to their own cells? BUT if it is in the 20% distribution, you just want to allocate them directly to the appropriate cells?

Pete

I must say that I am bad at explaining things. Let me try once again
First of all, I do not want to put the max value anywhere on the sheet. The only data entry (before running the code) would be, enter either A, B, C or D in cell F2
Assuming data entered in F2 is "A"
Run the code in my previous post. Here four values will get entered in range A1:D1. Value in cell A1 will be the max value because that is how the code is written (exit the loop when max value is found)
Now no matter how many times you run the code, you will ALWAYS get 4 values in range A1:D1 and A1 will ALWAYS have the max value

Changing the scenario:
Assuming data entered in F2 is "C"
Now if the code in my previous post is run, you will ALWAYS get 4 values in range A1:D1 and C1 will ALWAYS have the max value

Hope it fine till here.... and the code is also working fine as per requirement.

Now new condition - I need help here.

Have a look at the possibility code below. This code alerts how many times zero appeared in a loop of 100. (On an average, 20% of the times). Similarly, I want to change the above working code in such a way that, the code should run the way it is but 20% of the times it should give incorrect output.
For example,
Assuming data entered in F2 is "A"
Instead of outputting value in A1 as max ALWAYS , it should be 80% of the times correct by adding the j Mod 5 somewhere

Please Login or Register  to view this content.

One more thing is that, my code is very long and may not be the correct way to code. Please advice a better and efficient code that will suit my purpose. Phew!

Thank you.

Yes, I understand all that - I just didn't know what you wanted to do for the other 20% of the times.

Essentially, I thought you might want to swap the two values around, but your code in Post #5 will just put the maximum in the cell identified by F1 (and leave it in whatever cell it was in, so you could have two maxima displayed). I gave you a way of determining the 80% distribution earlier, i.e.:

Please Login or Register  to view this content.

and you want your comparisons to follow this, with an EndIF after them, so that the max is only positioned in the correct cell for 80% of the time - I don't see why you need a loop for this. Of course, it might be that the max value happens to be in the correct cell anyway, so it is not an exact 80% split.

Hope this helps.

Pete

Well.. I am still little confused.
I dont want to do anything of the other 20%. I do not want to swap either and yes... I want to put the max value as it is identified by F1. The only thing I need is the output of the code should pass (ss per conditions) 80% of the times and fail 20%
Now where should I keep the If WorksheetFunction.RandBetween(1, 5) <= 4 Then condition so that the code passes 75% to 80% of the times and fails 20% - 25% of the times?

Also, I have used worksheet functions in the code. Is there a vba way to write the randbetween and max? Appreciate if the logic be re-written in an efficient way.

I've been trying to decorate my bathroom today and I'm just checking for responses whenever I have a break. I'll take a more detailed look at this later on.

Pete

Finally I did it myself after a very long time. Please check the attachment for result. I am not getting an exact 80%:20% distribution but I am fine with the output that I am getting.
Now I need help in doing this in a better way and shorten the long code with a proper logic

Thank you

In the attached workbook I've put a data validation drop-down in F1, so you can only select the letters A to D. I also have this macro:

Please Login or Register  to view this content.

I've used an array called nums instead of 4 individually-named variables, and I've shown how you can use the VBA Rnd function instead of the RANDBETWEEN worksheet function. I've also given you an alternative way of deriving the MAX (and MIN) values without using the worksheet functions.

The macro will produce 100 rows of values in columns A to D, together with a count of the number of times the maximum appears in the chosen column, but you don't need the outer FOR loop after testing, as the comments indicate.

I don't do much programming nowadays, so this was a welcome interlude.

Hope this helps.

Pete

Hey... thanks. I just saw your reply. Not tried tough!
Just reading the code, I can say that you have taken care of sum of 100 and 80% of the time A1 will be the largest. However, there is one more condition. its not always A1. There is a condition here.... It depends on what is selected in F1.
If F1 contains "A" then A1 should be largest 80% of times, if F1 contains "B", then it should be B1 that should contain largest number and so on.... I see a sort option done for 80% condition so I am guessing the largest number will be in A1 ONLY and will not fulfill the second condition of checking the value in cell F1

I am going to try this and come back to you. Thank you

Originally Posted by freeofcost

It depends on what is selected in F1.
If F1 contains "A" then A1 should be largest 80% of times, if F1 contains "B", then it should be B1 that should contain largest number and so on.... I see a sort option done for 80% condition so I am guessing the largest number will be in A1 ONLY and will not fulfill the second condition of checking the value in cell F1

Exactly what I thought. 80% of the times, It ALWAYS puts the max number in A1 whereas I want 80% of the times, the max number to go in the column index specified in cell F1.

I suggested earlier that you needed to swap the numbers, so I have included a swap routine now so that the 4 numbers always add to 100.

Here is the modified code:

Please Login or Register  to view this content.

I don't think you understood that I was generating 100 rows for testing puposes, with G1 used to record the number of times the maximum occurs within the selected column (i.e. it represents the percentage that you refer to). You can run this macro several times quickly to see how that percentage varies. After testing, you should omit the lines coloured red in the code, and you also need to change the j in the line coloured blue to 1.

Hope this helps.

Pete

Originally Posted by Pete_UK

I suggested earlier that you needed to swap the numbers, so I have included a swap routine now so that the 4 numbers always add to 100. I don't think you understood that I was generating 100 rows for testing puposes, with G1 used to record the number of times the maximum occurs within the selected column (i.e. it represents the percentage that you refer to). You can run this macro several times quickly to see how that percentage varies. After testing, you should omit the lines coloured red in the code, and you also need to change the j in the line coloured blue to 1.

Oh! Yes... you are correct. I simply misunderstood and overlooked at the commented part. I ran the code once again 10 times to check the distribution after reading your explanation. Here is the result:
80, 74, 83, 85, 80, 81, 78, 78, 76, 81

The difference in both the codes 'try4' and 'try5' I saw was, in previous solution 'try4' the random numbers generated was not summing up to 100 which is resolved in present solution 'try5' with the swap routine. I understood the working of 'posMax' and 'posMin'

So sorry for the miscommunication. It works fine... Thank you so much.

Originally Posted by mikerickson

The third condition can be met by

I liked the idea of swapping columns to satisfy the third condition. Required distribution was 80:20 +/5 5%. I ran a test of 100 iteration 10 times to check the distribution. Here is the result:
81:19, 82:18, 79:21, 80:20, 84:16, 82:18, 82:18, 75:25, 82:18, 83:17
It works as per my desire. Thank you so very much.

One more point to discuss... In my test, I do not see NUMBER ZERO appearing anywhere in the generated random four numbers - What changes are required to include zeros too? It should appear on fewer occassions though... not always

Duplicates are allowed on fewer occassions and that is working fine

PS: There was a small typo. You omitted a space near SelectCase

If you want to slow it down then you need to increase the number of times you go round the final loop, either by increasing the final value or decreasing the step value (Why not just: For j = 1 To 100000, rather than have a STEP ?). If you do that, though, then the balls appear jerky, as A might change from, say, 73 to 24. The way to overcome that is to calculate the next set of random numbers, and then to have a series of intermediate values where you extrapolate from the start value (73) to the end value (24), and then the end value becomes the next start value. If you had, say, 100 intermediate values, then each increment would be (24-73)/100, and this would give you a much smoother transition on screen (you'll probably still have to play about with those values in the final loop).

I don't have chance to code this up at the moment, as Football is about to start on TV in about 10 minutes.

Hope this helps.

Pete

Thanks Pete... after reading your response I took a complete different approach of setting temporary values (a=0, b=100, c=0, d=100) and then have a loop to increase/decrease the temporary value from min to max. This will be a new sub routine altogether and once that is done, call the randomize subroutine. everything is working now. Thank you for your support