Excel

Question

I am using the T.DIST function and I am not getting the correct answer.

My equation is

=T.DIST(-16.84,499,TRUE) The answer I get is 5.12902E-51

There is another equation that is coming out correct

=T.DIST(-18.12,499,TRUE) and the answer is 0.00.

The second equation is being done on another computer and I do not have access to that one.

Is something wrong with the app or maybe a setting?

Answer 1

@Tamara54....

First, when the cell is formatted as General or Scientific, =T.DIST(-18.12, 499, TRUE) returns about 4.63E-57 on my computer and using Excel Online at onedrive.live.com.

If you see literally 0.00 on another computer, it is because the cell is formatted as Number with 2 decimal places, and 0.00 is just an illusion. On the other computer, confirm that =A1=0 returns FALSE(!), assuming the T.DIST formula is in A1.

Second, what makes you think that 5.13E-51 (rounded) and 4.63E-57 are wrong? What are you expecting, and why?

Finally, note that 5.13E-51 and 4.63E-57 are infinitesimally small numbers. They are approximately zero.

Even if T.DIST should return exactly zero with those parameters mathematically (but I doubt it!), most binary computer implementations are subject to the limitations and anomalies of (typically) 64-bit binary floating-point representation.

So, it is not unusual for the result of an Excel formula to be different from the mathematical formula. As a simple example, note that 10.1 - 10 = 0.1 returns FALSE(!). (LMK if you would like me to explain why.)

Generally, the work-around is: when you expect a calculation to be accurate to some number of decimal places, explicitly round to that number of decimal place -- and not to an arbitrary number of decimal places like 10.

For example, =ROUND(T.DIST(-16.84, 499, TRUE), 4), if you expect accuracy to 4 decimal places.

---

PS.... Literally T.DIST(-16.84, 499, TRUE) returns 5.13384546596352E-51, which rounds to 5.13385E-51, not 5.12902E-51.

So, either you posted the wrong example, or (more likely) the first parameter is not literally -16.84 as you wrote.

Obviously, when you ask a question about precision, it is important to show us the numerical inputs and results precisely.

For example, the first parameter might be -16.8400872802734, which rounds to -16.84. And T.DIST might return 5.12901956959532E-51, which rounds to 5.12902E-51.

Answer 2

Hello Tamara54,

Welcome to Microsoft community.

We also tested the equations you exemplified in your description and found that if you format the cells as numeric, the wrong equations give the correct results.

So, we would like to know, are you formatting the cells as "numeric"? Did you set the cell format to "Numeric" but still get the error?

We have to go through a series of steps to find the real cause of the problem. If you still have problems, please let me know and I will continue to help you with the problem.

Looking forward to hearing from you.

Sincerely,

Mavis - MSFT | Microsoft Community Support Specialist