1. Not finding help here? Sign up for a free 30min tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel formula for rounding a value & error

  1. Dec 3, 2015 #1
    I'm having a physics lab in which a report needs to be submitted after an experiment.
    The experiment includes many measurements: values with their errors.
    I need to round the error up to 2 significant digits, and then to round the value up to to the amount of digits that the error is presented.

    For example:
    * The measurement [tex]123.4567 \pm 0.489583[/tex] will be rounded to [tex]123.46 \pm 0.49[/tex]
    * The measurement [tex]123.4567 \pm 1.006[/tex] will be rounded to [tex]123.0 \pm 1.0[/tex]

    Instead of doing this manually, I thought to myself that probably someone already wrote a formula for Excel that will accomplish this, while considering all possible/problematic situations (unique scenarios).

    So this is my attempt at finding the formula.. did you wrote a formula like that? Or could you please refer me to a place that has it?

    Thank you..
     
  2. jcsd
  3. Dec 3, 2015 #2
    H i phy9:

    I assume you have separate cells for the value and the error range of a measurement. I also assume the the measurement values are the result of a calculation performed by a formula expression.

    I am not sure what you are trying to do. I see two possibilities:
    1. Do you want to control the appearance of what shows up in the cells of the spreadsheet?
    2. Do you want the content of the cell to be rounded?

    If (1), you would use the Format Cells feature to set the number of decimal places to be displayed for a particular cell.
    If (2), you can write an expression that will perform the rounding, and the value of the cell would be actually rounded as you may want. The trick is to multiply the value you want to round by a power of 10 so that all desired digits are to the left of the decimal point. Then you round to an integer by using INT(x+.5), where x is the expression for the result of the multiplication. Then you divide by the same power of 10 you originally multiplies by.

    Using your first expample, i assume you have a formula in a cell that results in 123.4567, say, "=a1*b1". You would modify this as:
    =INT(100*a1*b1+.5)/100​

    I hope this is helpful.

    Regards,
    Buzz
     
  4. Dec 3, 2015 #3

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    It depends what constraints should be satisfied.
    Consider 3.456±0.486 = 2.970 to 3.942
    Option 1: Round to nearest first: 3.456±0.486 => 3.46±0.49 = 2.97 to 3.95 = 3.46±0.49
    I would argue this is the least accurate.
    Option 2: Round to nearest after: 3.456±0.486 = 2.97 to 3.942 => 2.97 to 3.94 = 3.455±0.485 (ouch)
    Option 3: Round after, down for -, up for +: 3.456±0.486 = 2.97 to 3.942 => 2.97 to 3.95 = 3.46±0.49
    The rationale behind the third option is that you might want to ensure that the final answer encompasses the whole range of the original statement.
    Note that with different values, it can encounter the same ".005" problem as option 2.
    Isn't that the same as using the ROUND() function?
     
  5. Dec 4, 2015 #4
    Hi Buzz,

    I had thought of something like that but I'm looking for something that will round the numbers automatically in a given column, where one column is the value and the adjacent column is the error.
    This method is better, because that this way I don't need to refer to every cell of error & value (there are many measurements so it will take time..).

    ------

    Hi haruspex,

    Option 1 seems exactly what I'm looking for - these are the requirements of the course..

    I thought that there is a global standard of rounding values & errors regarding physics lab reports..
     
  6. Dec 10, 2015 #5
    Hi @haruspex:

    Unfortunately I can't at the present time check on what Excel does with ROUND. I have a vague recollection that it is not the same as the formula I posted for negative numbers. I also vaguely remember beginning to use the formula I posted rather than ROUND for some specific reason I don't now remember, but it may have been because of the way negatives were done.

    Regards,
    Buzz
     
  7. Dec 10, 2015 #6
    Hi @phy9:

    I don't understand what your specifications are for the rounding you want. How is the fixed formula you use to do the rounding going to "know" the number of decimal places you want? II think you mentioned that the following is what you want.
    Option 1: Round to nearest first: 3.456±0.486 => 3.46±0.49 = 2.97 to 3.95 = 3.46±0.49​
    This seems to be saying you want to round only the least significant decimal place in your data. But this doesn't match your examples.

    Can you write down the exact specification of what you want?

    Regards,
    Buzz
     
  8. Dec 10, 2015 #7

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    I can imagine it might do something mathematically inappropriate with negative arguments for ROUNDUP and ROUNDDOWN, but I'd be surprised if ROUND did anything weird.
     
  9. Dec 10, 2015 #8
    Hi haruspex:

    Unfortunately I can't test it now. I am not sure it does something weird. I think it may be something like not rounding an exact 0.5 in the direction I wanted for negative numbers. It was a long time ago.

    Regards,
    Buzz
     
  10. Dec 10, 2015 #9

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    I'll believe that, it's analogous to the issue I would anticipate with ROUNDUP/DOWN. But I wouldn't consider it serious since the "round halves up" convention is arbitrary. (In this regard at least, life would be simpler with an odd number system base.)
     
  11. Dec 14, 2015 #10

    SammyS

    User Avatar
    Staff Emeritus
    Science Advisor
    Homework Helper
    Gold Member

    Actually, rounding in a number system having an odd base isn't particularly easy,unless there is only a single digit beyond the place you're rounding to.

    For example, n base three (ternary) consider rounding 4.6 (decimal), to one's place.

    Easy enough in base ten (decimal).

    In ternary 4 and 6 tenths is written:
    11.12101210121...three

    It turns out that you need to do more than simply look the digit to the right of the place you're rounding to.

    1/2 is
    0.11111111111111...3 , so compare digits to that.
     
  12. Dec 14, 2015 #11

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    Sorry, I'm not seeing the difficulty. Could you spell it out a bit more, please?
     
  13. Dec 14, 2015 #12

    SammyS

    User Avatar
    Staff Emeritus
    Science Advisor
    Homework Helper
    Gold Member

    Suppose that you are to round the following number, written in base three, to the nearest whole number:

    11.121

    You can't simply look at the 1 to the right of the point.
     
  14. Dec 14, 2015 #13

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    Ok, I see. The rule becomes, if the digit you want to end at is a 1 then you need to scan right to the next 0 or 2 to decide.
     
  15. Dec 14, 2015 #14

    SammyS

    User Avatar
    Staff Emeritus
    Science Advisor
    Homework Helper
    Gold Member

    Yes.

    In base (2n - 1), scan to the right looking for the first digit greater than n or the first digit less than n.

    However, you are correct in noting that there will be no "ties" as there are with even bases.

    By the way, for other readers of this thread,

    the base three number, 11.121 , is 4 and 16/27 .
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Excel formula for rounding a value & error
Loading...