Excel formula for rounding a value & error

[phy9]

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 $$123.4567 \pm 0.489583$$ will be rounded to $$123.46 \pm 0.49$$
* The measurement $$123.4567 \pm 1.006$$ will be rounded to $$123.0 \pm 1.0$$

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..

 

[Buzz Bloom]

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

 

[haruspex]

possible/problematic situations

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.

=INT(100*a1*b1+.5)/100

Isn't that the same as using the ROUND() function?

 

[phy9]

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..

 

[Buzz Bloom]

Isn't that the same as using the ROUND() function?

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

 

[Buzz Bloom]

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.

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

 

[haruspex]

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

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.

 

[Buzz Bloom]

I'd be surprised if ROUND did anything weird.

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

 

[haruspex]

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

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.)

 

[SammyS]

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.)

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...₃ , so compare digits to that.

 

[haruspex]

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

Sorry, I'm not seeing the difficulty. Could you spell it out a bit more, please?

 

[SammyS]

Sorry, I'm not seeing the difficulty. Could you spell it out a bit more, please?

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.

 

[haruspex]

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.

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.

 

[SammyS]

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.

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 .