Results 1 to 15 of 19
Thread: Counting (Excel 2000)

20040101, 22:58 #1
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Counting (Excel 2000)
I'm using an Excel addin 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?

20040102, 03:01 #2
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 18. 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

20040102, 16:06 #3
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040102, 16:45 #4
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040103, 02:19 #5
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040104, 13:19 #6
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040104, 14:22 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040104, 15:32 #8
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 "=B2MIN(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 28.

20040104, 16:25 #9
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040104, 16:36 #10
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040104, 16:59 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040108, 15:36 #12
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (Excel 2000)
Thanks for the help guys. I've got just one more questionis it possible to erase the contents of a cell without removing the formula?.
Paul

20040108, 16:02 #13
 Join Date
 Jan 2004
 Location
 Derry, Derry, Ireland, Northern
 Posts
 35
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040108, 17:30 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Counting (Excel 2000)
You can also fornat with ";;;" to "hide" the contents
Steve

20040108, 19:57 #15
 Join Date
 Jan 2002
 Location
 Cape Cod
 Posts
 117
 Thanks
 0
 Thanked 0 Times in 0 Posts
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