if / exact problem
 

hi

i have a column of figures i am trying to balance, visually they match but i
cant get the formula to read it that way.


the sheet is laid out like this

A B
1 Title $
2 Pre-X 3507.55975
3 Mid-X -1154.193125
4 Post-X 0.00000
5 Total 2353.366625


to reconcile the figure in cell B4, i have this formula in cell B6;


=IF(B4=B5-B3-B2,TRUE,FALSE)

this should work fine, but it comes up saying FALSE

this is doing my head in!


if i break the IF formula down & put cell D1 as;

=B4


& cell D2 is;

=B5-B3-B2



& then put this in cell D3;


=Exact(D1,D2)


it comes up as TRUE


so the numbers DO actually match (as you would expect!!!), why then is my IF
function mucking about?



has anybody come across this before? i am think this maybe has something to
do with it reading zeros differently?


any help gratefully recieved


cheers

jb

if / exact problem
 

Toppers, thanks for your help on this one as well!

round to 6 places worked great, every other formula on this sheet ive
inherited is using ROUNDUP to no dec places, this particular set of data is
obviously more sensitive due to its scale


thanks again


jb

"Toppers" wrote:

Try:

=IF(ROUND(B4,6)=ROUND((B5-B3-B2),6),TRUE,FALSE)

"JB2010" wrote:

hi

i have a column of figures i am trying to balance, visually they match but i
cant get the formula to read it that way.


the sheet is laid out like this

A B
1 Title $
2 Pre-X 3507.55975
3 Mid-X -1154.193125
4 Post-X 0.00000
5 Total 2353.366625


to reconcile the figure in cell B4, i have this formula in cell B6;


=IF(B4=B5-B3-B2,TRUE,FALSE)

this should work fine, but it comes up saying FALSE

this is doing my head in!


if i break the IF formula down & put cell D1 as;

=B4


& cell D2 is;

=B5-B3-B2



& then put this in cell D3;


=Exact(D1,D2)


it comes up as TRUE


so the numbers DO actually match (as you would expect!!!), why then is my IF
function mucking about?



has anybody come across this before? i am think this maybe has something to
do with it reading zeros differently?


any help gratefully recieved


cheers

jb

if / exact problem
 

Try:

=IF(ROUND(B4,6)=ROUND((B5-B3-B2),6),TRUE,FALSE)

"JB2010" wrote:

hi

i have a column of figures i am trying to balance, visually they match but i
cant get the formula to read it that way.


the sheet is laid out like this

A B
1 Title $
2 Pre-X 3507.55975
3 Mid-X -1154.193125
4 Post-X 0.00000
5 Total 2353.366625


to reconcile the figure in cell B4, i have this formula in cell B6;


=IF(B4=B5-B3-B2,TRUE,FALSE)

this should work fine, but it comes up saying FALSE

this is doing my head in!


if i break the IF formula down & put cell D1 as;

=B4


& cell D2 is;

=B5-B3-B2



& then put this in cell D3;


=Exact(D1,D2)


it comes up as TRUE


so the numbers DO actually match (as you would expect!!!), why then is my IF
function mucking about?



has anybody come across this before? i am think this maybe has something to
do with it reading zeros differently?


any help gratefully recieved


cheers

jb

if / exact problem
 

Either your other calculations involved only integers, or you were just lucky.

Most decimal fractions (including all three that you listed) have no exact
binary representation, and hence must be approximated. Therefore it is
unreasonable to expect that the results of different calculations will
necessarily result in the same approximations to the exact result. The
standard approaches are to either ask if their difference is suitably small,
or to ask if their rounded values are equal.

Jerry

"JB2010" wrote:

Toppers, thanks for your help on this one as well!

round to 6 places worked great, every other formula on this sheet ive
inherited is using ROUNDUP to no dec places, this particular set of data is
obviously more sensitive due to its scale


thanks again


jb

"Toppers" wrote:

Try:

=IF(ROUND(B4,6)=ROUND((B5-B3-B2),6),TRUE,FALSE)

"JB2010" wrote:

hi

i have a column of figures i am trying to balance, visually they match but i
cant get the formula to read it that way.


the sheet is laid out like this

A B
1 Title $
2 Pre-X 3507.55975
3 Mid-X -1154.193125
4 Post-X 0.00000
5 Total 2353.366625


to reconcile the figure in cell B4, i have this formula in cell B6;


=IF(B4=B5-B3-B2,TRUE,FALSE)

this should work fine, but it comes up saying FALSE

this is doing my head in!


if i break the IF formula down & put cell D1 as;

=B4


& cell D2 is;

=B5-B3-B2



& then put this in cell D3;


=Exact(D1,D2)


it comes up as TRUE


so the numbers DO actually match (as you would expect!!!), why then is my IF
function mucking about?



has anybody come across this before? i am think this maybe has something to
do with it reading zeros differently?


any help gratefully recieved


cheers

jb