# How Excel calculates values for conditions

1. ## How Excel calculates values for conditions

I've encountered a very simple but irritating problem with Excel. It's to do with how Excel calculates functions of cell values, and how this relates to IF conditions.

Its best to use an example.

In cells A1, A2, A3 and A4 type the following values -

20
0.4
0.1
6

In cell A5, calculate (A1*(A2-A3))-A4. This will evaluate to 0.
In cells A6, B6 and C6, type A5<0, A5=0, A5>0.
A6 should be FALSE, B6 should be TRUE, and C6 should be FALSE.

BUT now try typing (A1* (A2-A3))-A4<0, (A1* (A2-A3))-A4=0, (A1* (A2-A3))-A4>0.

This time the values will be FALSE, FALSE, TRUE. In other words, this time it evaluates the value as being >0, even though the two different methods condense to the same process.

This is incredibly frustrating as my spreadsheet requires these sort of conditions to be evaluated accurately, but I am not sure how Excel works in terms of evaluating formulas. If I want to do more complicated formulas, but using the same <0, =0, >0 evaluation as above, do I have to get Excel to compute each separate bit of the formula to get it to evaluate the conditions correctly?

2. ## Re: How Excel calculates values for conditions

Not ideal I know, but you can get round it via

=ROUND((A1*(A2-A3))-A4,10)<0

I guess that a floating point imprecision is at work...

3. ## Re: How Excel calculates values for conditions

okay cheers, so does Excel stop at 10 decimals, i.e. it can't compute values more accurately than this due to how its programmed?

4. ## Re: How Excel calculates values for conditions

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1