microsoft excel - Math Round multiple cells, generate integers and histogram integer values

06
2014-04
  • Teodorescu

    I want your help for the following points:

    1. How generate integer values using Data menu > Data Analysis > Random Number Generation or (if this action can't be done)

    2. how round multiple cells in same time ? I have 120 generated values (using Random Number Generation) and avoid writing 120 times ROUND(A1, 0) ... ROUND(A120, 0), etc...

    3. When I want to create a histogram with integer values, I can't because Excel returns (Data menu > Data Analysis > Histogram) at Bin field (after him is Frequency field), double values (with comma) .

    • Answers
  • ford

    The typical way to perform the same action on many cells is to create the data in columns. The first column is your data, the column to the right of that is your data after that action. So, you would put =ROUND(A1,0) in cell B1. Now, drag the bottom right corner of B1 down the full length of the data in A. The B column will be automatically filled in with =ROUND(A1,0), =ROUND(A2,0) ... =ROUND(A120,0).

    Now, to answer your question more specifically. If you want random integers, a simple way to do it is enter in a cell:

    =INT(RAND()*100)

    This you can then drag down the bottom right corner of that cell just like I described above. You will end up with a column of random integers between 0 and 99.

    Unfortunately, I don't have the Data Analysis tools in my version of Excel, and I don't quite understand your third question anyway. Can you clarify it a bit?


    • Related Question

    worksheet function - Excel Formula: trouble with the low % values not showing up in an in-cell chart? (image included)
  • RocketGoal

    I've a few tables that have % values like -4.5%; -16%; 15%; 6%; 2.5%. I'm interested in creating some in-Cell charts that show positive and negative values.

    My smaller values aren't showing up, so I've used some larger %'s to see what happens (See small image). It's only near the 200% and above, or -200% and below that the bar starts to show (the bar is in Column Y2).

    alt text.

    The formula in the top right cell is below, and this is copied down accordingly (i've left % in the lower rows by accident,but it doesn't change anything...).

    =IF($W3>0,REPT($Y$2,ROUND($W3/2,1)),REPT($Y$2,ROUND(-$W3/2,1)))

    And my actual workbook is set up as follows.

    alt text

    What should I do to this formula to make the lower figures show up? Even if the larger figure shoots of the screen.

    Many thanks for your help.

    Michael.


    • Related Answers
  • Mike Fitzpatrick

    That's because 100% in Excel is actually 1.0, not 100.0

    In most cases here you are telling REPT to repeat something less than once, so it prints nothing.

    Instead of dividing by 2 you should be multiplying by (100 / inc) where inc is the percentage points you want per repeated text.