Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    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 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. #2
    Platinum Lounger
    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 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. #3
    2 Star Lounger
    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

  4. #4
    2 Star Lounger
    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?

  5. #5
    Platinum Lounger
    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

  6. #6
    2 Star Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Platinum Lounger
    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 "=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. #9
    2 Star Lounger
    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

  10. #10
    2 Star Lounger
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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

  12. #12
    2 Star Lounger
    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 question--is it possible to erase the contents of a cell without removing the formula?.

    Paul

  13. #13
    Lounger
    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?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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

  15. #15
    2 Star Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

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