Excel help

Etta2001!

I wrote this formula on Excel: =E2*(A2-B2-C2).
There's numbers in the relevant cells and it should output 0 but it's outputting this strange result: -2.8E-17.
Please could anyone help me identify why it outputted such a weird result? Why does it have an "E" in it anyway when all the inputs are integers?

Reply 1

MindMax2000

Universities Forum Helper

21

Original post by holycookie

I wrote this formula on Excel: =E2*(A2-B2-C2).
There's numbers in the relevant cells and it should output 0 but it's outputting this strange result: -2.8E-17.
Please could anyone help me identify why it outputted such a weird result? Why does it have an "E" in it anyway when all the inputs are integers?

The resulting E-17 means exponential to base 10 i.e. 10^-17

I can't say specifically why you get this result, but it could be the way you typed E2, or there is something you typed into either the formula or cells that shouldn't be there.
Try the same formula with the same data on a new sheet to check.

Reply 2

Etta2001!

OP

12

Original post by MindMax2000

The resulting E-17 means exponential to base 10 i.e. 10^-17

I can't say specifically why you get this result, but it could be the way you typed E2, or there is something you typed into either the formula or cells that shouldn't be there.
Try the same formula with the same data on a new sheet to check.

Thanks for your reply! I tried it with just =(A2-B2-C2) and I think it is definitely something to do with the brackets in (A2-B2-C2). Still no idea why it outputted that, thanks anyway.

Reply 3

davros

16

Original post by holycookie

I wrote this formula on Excel: =E2*(A2-B2-C2).
There's numbers in the relevant cells and it should output 0 but it's outputting this strange result: -2.8E-17.
Please could anyone help me identify why it outputted such a weird result? Why does it have an "E" in it anyway when all the inputs are integers?

Do any of the numbers in A2, B2, C2 or E2 contain decimals or are they the results of some other calculations?

-2.8E-17 means

-2.8 \times 10^{-17}

which is a very tiny number close to 0. This usually happens when Excel is trying to store numbers internally that are not exact so its calculations come out close to a whole number but not exactly. If you're confident that the answer should be a whole number you could use one of the rounding functions like =ROUND() around the output, but only if you're sure that you will never get a decimal part in your expected answer.

Reply 4

HedgePig

14

Agree that it’s because Excel (like any program) cannot handle the calculation perfectly accurately due to the way fractions are stored.

You could either
1. Use the ROUND function to round off to a specified number of decimal places, or
2. Format the answer to display a fixed number of decimal places.

(edited 3 years ago)

Reply 5

MindMax2000

Universities Forum Helper

21

Original post by holycookie

Thanks for your reply! I tried it with just =(A2-B2-C2) and I think it is definitely something to do with the brackets in (A2-B2-C2). Still no idea why it outputted that, thanks anyway.

One thing I would try is to increase the decimal numbers to see whether there are any hidden decimals in your cells for Row 2.
Highlight the 4 cells, and go to the Home ribbon>Numbers section. You should see the 0, 0.00 icons, click on the one with the arrow pointing to the left.

Excel help

Quick Reply

Related discussions

Latest

Trending

Trending

Articles for you