- #1

e2m2a

- 354

- 11

- TL;DR Summary
- Excel giving strange results. Seems to fail for taking square roots of very large odd powered numbers.

Is it always true that the square root of an odd powered integer will always be irrational?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- B
- Thread starter e2m2a
- Start date

- #1

e2m2a

- 354

- 11

- TL;DR Summary
- Excel giving strange results. Seems to fail for taking square roots of very large odd powered numbers.

Is it always true that the square root of an odd powered integer will always be irrational?

- #2

- 23,219

- 14,723

What about ##\sqrt{4^3} = 8##?Summary::Excel giving strange results. Seems to fail for taking square roots of very large odd powered numbers.

Is it always true that the square root of an odd powered integer will always be irrational?

- #3

e2m2a

- 354

- 11

- #4

- 23,219

- 14,723

Take any positive integer ##n##. Either:

a) ##n## is a perfect square, hence ##\sqrt n## is a postive integer.

or

b) ##\sqrt n## is irrational.

In other words, there is no positive integer ##n## where:

c) ##\sqrt n## is a proper rational. I.e. rational but not an integer.

Proving this is a useful exercise.

- #5

e2m2a

- 354

- 11

- #6

mfb

Mentor

- 36,167

- 13,154

Correct, that's also what post 4 says. It's pretty easy to prove.

- #7

pbuk

Science Advisor

Gold Member

- 3,864

- 2,244

Fail in what way?Summary::Excel giving strange results. Seems to fail for taking square roots of very large odd powered numbers.

Whether something is irrational or not has absolutely no bearing on the accuracy of any compution in Excel.Is it always true that the square root of an odd powered integer will always be irrational?

- #8

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

Except, of course, to the extent that no irrational number can be a model number in IEEE 754 floating point. An integer might or might not be.Whether something is irrational or not has absolutely no bearing on the accuracy of any compution in Excel.

- #9

e2m2a

- 354

- 11

Excel rounds off the square root of a very large odd powered integer so that it gives an integer value. I have tested this with very large numbers. This is why I needed to research if the square root of odd powered integers always yields an irrational number. Excel like any other computer programs have a limitation on their accuracy.Fail in what way?

Whether something is irrational or not has absolutely no bearing on the accuracy of any compution in Excel.

- #10

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

Can you give an example? "Very large" is not very exact.Excel rounds off the square root of a very large odd powered integer so that it gives an integer value. I have tested this with very large numbers. This is why I needed to research if the square root of odd powered integers always yields an irrational number. Excel like any other computer programs have a limitation on their accuracy.

Edit: figured it out for you...

By default, Excel displays cells in "General" format. Using this format, you can put a formula into a cell and see a number displayed. For instance, =sqrt(3^41).

Under the default "General" format, this displays as 6039287738

Under the "Number" format, this displays as 6039287737.57

Under the "Accounting" format, this displays as 6,039,287,737.57

Under the "Number" format with 10 decimal places, this displays at 6039287737.5706100000

One assumes that those last five digits were simply zero filled. That is 15 or maybe 16 decimal digits of precision. Which is about right for a 64 bit IEEE float.

Last edited:

- #11

pasmith

Homework Helper

- 2,424

- 1,029

Excel rounds off the square root of a very large odd powered integer so that it gives an integer value. I have tested this with very large numbers. This is why I needed to research if the square root of odd powered integers always yields an irrational number. Excel like any other computer programs have a limitation on their accuracy.

Excel might not do that internally, but the number of significant digits it displays might make it look like it does that.

- #12

e2m2a

- 354

- 11

Raise 7 to the 39th power in excel and then take the square root. The answer should be an irrational number but it shows up as an integer with zeroes in the decimal place.Can you give an example? "Very large" is not very exact.

Edit: figured it out for you...

By default, Excel displays cells in "General" format. Using this format, you can put a formula into a cell and see a number displayed. For instance, =sqrt(3^41).

Under the default "General" format, this displays as 6039287738

Under the "Number" format, this displays as 6039287737.57

Under the "Accounting" format, this displays as 6,039,287,737.57

Under the "Number" format with 10 decimal places, this displays at 6039287737.5706100000

One assumes that those last five digits were simply zero filled. That is 15 or maybe 16 decimal digits of precision. Which is about right for a 64 bit IEEE float.

- #13

- 23,219

- 14,723

When I try I get (same as using Python):Raise 7 to the 39th power in excel and then take the square root. The answer should be an irrational number but it shows up as an integer with zeroes in the decimal place.

3.01586E+16 |

- #14

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

15 digits of precision displayed, followed by zeroes. The underlying implementation is 64 bit floating point. Apparently rounded to 15 decimal digits.Raise 7 to the 39th power in excel and then take the square root. The answer should be an irrational number but it shows up as an integer with zeroes in the decimal place.

https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

- #15

Mark44

Mentor

- 36,429

- 8,409

The IEEE 754 Standard for floating point 64-bit numbers maintains slightly over 15 decimal digit precision. See https://en.wikipedia.org/wiki/IEEE_754.15 digits of precision displayed, followed by zeroes. The underlying implementation is 64 bit floating point. Apparently rounded to 15 decimal digits.

- #16

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

Right. But Excel seems to consistently chop them off at 15.The IEEE 754 Standard for floating point 64-bit numbers maintains slightly over 15 decimal digit precision. See https://en.wikipedia.org/wiki/IEEE_754.

Try keying 1234567890123456 into a cell and set the display format to number (default 2 digits to the right of the decimal point). The six is discarded in favor of a zero. Same if you key in 1234567890.123456. [Excel actually truncates the cell contents in these cases, not just the displayed value]

Or, equally dramatic, try =111111111*111111111 (9 digits times 9 digits) for a result of 12345678987654300 (off by 21).

It is a reasonable strategy, producing zeroes rather than digits of questionable accuracy.

Last edited:

- #17

mfb

Mentor

- 36,167

- 13,154

- #18

pasmith

Homework Helper

- 2,424

- 1,029

[itex]39\log7 \approx 32.96[/itex]. I would not expect the result of [itex]7^{39}[/itex] to be accurate, still less the result of the square root.Raise 7 to the 39th power in excel and then take the square root. The answer should be an irrational number but it shows up as an integer with zeroes in the decimal place.

- #19

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

Nicely designed experiment. The result is 120. Based on this result, the intermediate result used by Excel was 12345678987654320 exactly. This fits the IEEE 754 expectation.

Expressed in binary, the product 111111111*111111111 has 54 bits. IEEE 754 can express 53 (counting one extra "hidden" bit from normalization). Here is the true mathematical result for the product. [Result obtained from Windows calculator, programmer mode]

Code:

```
10 1011 1101 1100 0101 0100 0110 0010 1001 0001 1111 0100 1011 0001
<---------------------------- 52 bits -------------------------->
^ ^
One "hidden" bit from normalization One bit not expressed
```

The value that you asked to be subtracted (12345678987654200) is a model number which also fits in 15 decimal digits. (All 15 digit integers are model numbers within the 64 bit IEEE floats). Again, my compliments on the chosen probe.

One can get more information on IEEE 754 from the wiki page. I've extracted a summary here.

Digression alert...

Many programmers treat floating point as being inherently inexact. These individuals usually display a healthy paranoia in their programs. However, floating point has exact and predictable behavior.

Forty years ago I was using 64 bit floating point in an accounting environment for correct-to-the-penny calculations. [I hate bean counters]. Our hardware and programming languages provided no direct support for 64 bit integer arithmetic. Fortunately, the hardware and software did support 56 bit significands in its 64 bit (VAX D floating) format. Put an integer number of pennies into a D float and everything works fine as long as you are dealing with less than 720 trillion dollars or so.

Last edited:

- #20

pbuk

Science Advisor

Gold Member

- 3,864

- 2,244

Excel like any other computer programs have a limitation on their accuracy.

But again this has nothing to do with rational vs irrational: Excel does not calculate

`sqrt(7^40)`

exactly either.- #21

sysprog

- 2,613

- 1,782

Windows 10 Calculator shows 111111111 x 111111111 = 12345678987654321 ##-## maybe the Excel guys should try to glean something from the Calculator code, now that it's open source (github.com/microsoft/calculator).

- #22

pbuk

Science Advisor

Gold Member

- 3,864

- 2,244

The limitations of IEEE 754 are well suited to (and well understood by computational experts in) almost all applied scientific and financial purposes, which is what Excel is mainly used for.

If you want arbitrary precision, rational or Big Integer arithmetic then use a package that supports that.

- #23

sysprog

- 2,613

- 1,782

Yeah, when I increase the number of 1s to 17, the result is 1.2345679012345678765432098765432e+32.

The limitations of IEEE 754 are well suited to (and well understood by computational experts in) almost all applied scientific and financial purposes, which is what Excel is mainly used for.

If you want arbitrary precision, rational or Big Integer arithmetic then use a package that supports that.

- #24

mfb

Mentor

- 36,167

- 13,154

Zimbabwe would like to have aand everything works fine as long as you are dealing with less than 720 trillion dollars or so.

There are a few bank errors which can give some insight. This 11.7 quadrillion bill was simply adding a lot of zeros to the real bill (they offered a repayment plan!), but this $23 quadrillion dollar balance has a lot of different digits. No obvious pattern in decimal or binary dollars - but in cents, it's 10000000100000001000000010000000100000001000000001001001010000_2 or 2020202020201250_16. The pattern here is obvious. The last digits are caused by actual account transactions, and a computer error added a lot of 0x20 bytes to it. There are more than 53 non-zero binary digits and 62 in total. This suggests the bank uses 64 bit integers.

- #25

Svein

Science Advisor

- 2,249

- 773

ASCII 0x20 is " " (space). Don't blame the computer - as usual, the error can be traced to one or more inept programmers.The last digits are caused by actual account transactions, and a computer error added a lot of 0x20 bytes to it.

- #26

mfb

Mentor

- 36,167

- 13,154

I'd like to deposit

`" "`

in my bank account please. That should be free, right?- #27

Nugatory

Mentor

- 14,041

- 7,681

Yes, although that statement might easily be misunderstood by someone who hasn’t been through the wars. As many pre-IEEE floating point designs demonstrated, “exact and predictable” does not preclude “bizarre and surprising”.However, floating point has exact and predictable behavior.

IEEE-754 is one of the underappreciated triumphs of computing, making the world safer for the naive every day.

- #28

BWV

- 1,225

- 1,365

The nth root of x given by =int(x^(1/n)) in Excel is an integer ;)

- #29

jbriggs444

Science Advisor

Homework Helper

- 11,416

- 6,031

Are you suggesting something like...The nth root of x given by =int(x^(1/n)) in Excel is an integer ;)

Put some positive integer into A1

Put some positive integer into A2

Put =A1^A2 into A3

Put

Observe that A4 is equal to A1 unless an overflow has occurred while computing A3

- #30

BWV

- 1,225

- 1,365

It was just an attempt at a joke, but per the OP x would be a^some odd power and n would be 1/2Are you suggesting something like...

Put some positive integer into A1

Put some positive integer into A2

Put =A1^A2 into A3

Put=INT(A3^(1/A2))into A4

Observe that A4 is equal to A1 unless an overflow has occurred while computing A3

- #31

Baluncore

Science Advisor

2021 Award

- 11,726

- 5,924

Did you notice that if you write any of those irrational square roots as a continued fraction, they always repeat.

- #32

sysprog

- 2,613

- 1,782

What about 1?Is it always true that the square root of an odd powered integer will always be irrational?

Share:

- Replies
- 9

- Views
- 446

- Last Post

- Replies
- 11

- Views
- 472

- Replies
- 3

- Views
- 440

- Last Post

- Replies
- 4

- Views
- 859

- Replies
- 1

- Views
- 235

- Last Post

- Replies
- 1

- Views
- 257

- Replies
- 13

- Views
- 2K

- Last Post

- Replies
- 5

- Views
- 642

- Replies
- 15

- Views
- 609

MHB
An integer

- Last Post

- Replies
- 2

- Views
- 519