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
Bookmarks