Generate random values that are a function of % calculations

Experts:

I need some assistance with generating some *random numbers* for four (4) metrics. However, the random numbers are a functions of several % calculations.

The four metrics are (I'm using acronyms for demo purposes only):
"A"
"B"
"C"
"D"

Also, for each of the four metrics (A:D), I have calculations for 16 systems (1:16).

At this moment, I recommend to open the attached XLS... I think it'll help to better follow along w/ the % calculations:

Example Data:
- I have the 4 metrics (A:D) in cells A3:A6
- I have example data (25, 49, 21, 42) in cells B3:B6
- Now, cells A11:A14 shows the calculations/functions for the four metrics.
- For instance, the 1st metric = "A/B". Thus, once I divide 25 by 49, I get 51%... check on the % value in B11.
- Alternatively, the 2nd metric = "C/D". Thus, once I divide 21 by 42, I get 50%... check on the % value in B12.
- Similarly, 3rd metric = "A/D" (60%) while 4th metric = "C/A" (84%).
- Again, the values in cells B3:B6 are sample values.

What I need some help with:
- Now, for cells C3:Q6, I do NOT have any sample data.
- So, I want to populate C3:Q6 with *random* data.
- The random data, however, must give me the % values (which I do have) listed in C11:Q14.
- I learned it is easy to generate the 1st metric. That is, I used, e.g., =Randbetween(10,99) in cell C22:Q22.
- Next, using simple multiplication, I chose formula "=C11*C22" to populate row 21.
- Thus, in my temporary validation section (rows 27 through 32), row 29 shows the correct output. That is, % values in row 29 match those of row 11...
- However, once I continue on to derive at some of the other random values, my existing percentage calculations in rows 29:32 no longer match those in rows 11:14.

My question:
Is there a way to use formulas to derive at the correct random values (in rows 21:24) so that all of my calculations in rows 29:32 match the required % values listed in rows 11:14?

EEH

The values in rows 3 to 6 must be positive integers? Is there any range besides 10 to 99 for row 4 (metric B)?

Also, your percentages are rounded. For C11:C14, C13 means A = .78 D. However, C12 means C = .69 D, and C14 means A = C / .88. Perform some substitutions: .78 D = C / .88, so C = (.78) (.88) D = .6864 D . 0.6864 is close to 69%, but not exact.

If you want smallish integers which produce metrics close enough to the targets, you'd be better off with a NONSTOCHASTIC approach.

For example, define the name B referring to =ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,99,1)). That makes B the array {1;2;3; . . . ;97;98;99}. Then in C4, enter this array formula monstrosity.

C4:

Formula:

Please Login or Register  to view this content.

This is the smallest integer in B for which the various ratios of integers in C3:C6 rounded to 2 decimal places are effectively equal to the percentages in C11:C14.

C3:

Formula:

Please Login or Register  to view this content.

C5:

Formula:

Please Login or Register  to view this content.

C6:

Formula:

Please Login or Register  to view this content.

Select C3:C6 and fill right into D3:Q6. When you do so, you'll find that P3:Q6 all show #N/A, which means there isn't an integer in 1 to 99 for P4 or Q4 which would combine with other smallish integers in the other cells in P3:Q6 which would produce rounded percentages close enough to those in P11:Q14.

However, that's why one uses defined names. Change the definition of B to =ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,199,1)). Once you do so, P4 becomes 110 and Q4 becomes 103.

The main thing to note is that if all cells in B3:Q6 must be integers, and the percentages are rounded, so integers would produce percentages within 0.5% of the percentages shown, then this is a relatively simple linear programming problem rather than a stochastic simulation problem.

hrlngrv -- thank you for the work... I'm not entirely certain I can produce the results right now. Let me recap.

- I copied the formula below into cell C4 with Shift+Enter:

Please Login or Register  to view this content.

- If so, I'm getting a "#NAME?" error at this time.
- Just to be certain I didn't miss anything in your post, I then copied "=ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,199,1))" into C4. It returned a "1".
- Next, I copied the remaining 3 formulas into C3, C5, and C6. All of the returned values are binary though.

If a binary response is intended then I must have missed something in my original post. Ultimately, here's what I'm trying to achieve:
- First, see cells B3:B6. Here, I have some integer values (25, 49, 21, and 42).
- For this example, I want all values in C3:Q6 also to be integer values.
- Ideally, I started with random values between 20 and 99. However, rand values between 20 and 500 are ok, too.

Any additional thoughts on how I can achieve the population of C3:Q6 with (rounded to 0 decimals) integer values between 20 and 500?

MrShorty - thank you for the URL... I will look into it.

For testing purposes, I added the following formulas into C21:C24, respectively:
=C11*C22
=RANDBETWEEN(20,99)
=C21/C24
=C21*C14

Now looking at the % validations in rows 29:32, the only ones that are correct are those for metric A in row 29. What did I miss?

MrShorty - thank you for the prompt response. Your solution works perfectly! Thousand thanks!

You must also define the name B. Don't enter its definition in C4. It does nothing useful there.

hrlngrv - I downloaded the XLS but received only "#Name?" errors. See attached JPG.

I reopened my copy, and it also had problems with the name B. I've fixed it.

I embellished it by displaying the definition of B in the worksheet, and that required changing to .XLSB file format to be able to use and save XLM functions.

Looks good to me... thank you for providing the alternative solution. Appreciate your help on this activity.