1. ## Counting (Excel 2000)

I'm using an Excel add-in to run Monte Carlo Simulations. There are 8 Slots and 8 corresponding numbers :
SLOT NUMBERS
1 32.56
2 31.59
3 32.80
to 8

10,000 iteretions. After each I would like to record which slot has the minimum number. After completion of the run
a summary of each slot and the number of times a minimum number appeared.

I know that the MIN function can determine the smallest number but how do I count?

2. ## Re: Counting (Excel 2000)

The problem here appears to be the occurrence of circular references in the cumulative counter for each slot. I'm guessing it might be a better candidate for VBA than worksheet functions. But it's possible to "legitimize" such a circular reference via Tools -> Options -> Calculation, checking Iteration and setting Maximum Iterations to 1.

The attached sheet might do something like you're trying to achieve. I've used RAND() to generate the numbers that appear in slots 1-8. If you start typing into an empty cell, these values will change and the "Cumulative Totals" column will tally the way you describe. Not very polished, but might be a start.

Alan

3. ## Re: Counting (Excel 2000)

Hi 007, Exactly what I was looking for. So simple yet I couldn't figure it out. That's why you guys make the big money.

pjb

4. ## Re: Counting (Excel 2000)

007, I just noticed that sometime there are duplicate minimum numbers and only the first one is extended. How do I pick up the second duplicate?

5. ## Re: Counting (Excel 2000)

I'm not sure if you're referring to my concoction here, or your Monte Carlo simulator. Certainly in my version, the RAND() function will be generating numbers between 0 and 1 to a precision of 15 decimal places. These will only display to 8 places in the cells, so that two numbers that appear to be equal may well be different.

If they are genuinely the same, I can't see why both counters should not increment. Have you tried it with the low precision numbers you want to work with? I don't know how your numbers are generated, but if it's by calculation then you may be actually dealing with numbers of a much higher precision than you'd planned on. This high precision may also be masked by the kind of cell formatting you're using. If this is the case, I'd suggest incorporating the ROUND() function into your formulae to avoid the apparent equality of numbers that aren't actually equal.

One last question: When is this "big money" supposed to start rolling in? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Alan

6. ## Re: Counting (Excel 2000)

Alan, I'm refering to the MIN function. It goes down the list, finds the first minimum number. If there are duplicates only the first minimum number is acted on. How do I get the 2nd number to be counted as a minimum also?

Paul

7. ## Re: Counting (Excel 2000)

Alan's method will find multiple mins and act on them.

Are you sure that the values are identical to 15 decimal places?

You could display 32.35 in 2 slots but one might be 32.346 and the other 32.351 and the min is 32.346.

If you want to look at the min of "rounded" values you would have to adjust for this.

Steve

8. ## Re: Counting (Excel 2000)

Paul

I suggest you check on whether the second value is really equal to the value generated by the MIN function. If you don't follow the explanation I gave, format the cells in column F in the sheet I posted to "Number" and set decimal places to 15. Then insert the formula "=B2-MIN(numbers)". If the result is 0.000000000000000 for any of the cells, then it is really equal to the minimum. Otherwise it's not. Perhaps you could post a sheet showing the particular instance that's bothering you.

Alan

Edited - Should read: insert the formula ... into cell F2 and drag down column F to fill for slots 2-8.

9. ## Re: Counting (Excel 2000)

Hi Steve and Alan, There are only 2 decimal places i.e. 31.xx. There is never a third. These numbers are times in hundreds of a second and sometimes there are duplicates that have to be treated as the minimum of the range.

Paul

10. ## Re: Counting (Excel 2000)

Alan and Steve, Sorry guys, your right. the numbers are computer generated and they are not equal even when I round them off. Can I round them off to two places and make them equal?
Paul

11. ## Re: Counting (Excel 2000)

You could use in COl C of Alan's spreadsheet:

<pre>=IF(ROUND(B2,2)=ROUND(MIN(numbers),2),A2,0) </pre>

to compare rounding to 2 places to the min rounded to 2 places

Steve

12. ## Re: Counting (Excel 2000)

Thanks for the help guys. I've got just one more question--is it possible to erase the contents of a cell without removing the formula?.

Paul

13. ## Re: Counting (Excel 2000)

hI Paul,

If you have a formula in a cell, what you 'see' in the cell is a result. The 'contents' of the cell is a formula, and you can't erase what you see in the cell without deleting the formula. You *could* make the font colour of the cell white (or make it make the background colour of the cell, if it's not white), but then you wouldn't see the result.

Any particular reason for what you want to do?

14. ## Re: Counting (Excel 2000)

You can also fornat with ";;;" to "hide" the contents

Steve

15. ## Re: Counting (Excel 2000)

Hi Meloncolly, I want to run a simulation, get the results, start with a clean worksheet and do it again without reentering the formulas everytime.

Paul

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•