SOLVED

Why would this formula work in some cells and not others? Please help!

%3CLINGO-SUB%20id%3D%22lingo-sub-1806491%22%20slang%3D%22en-US%22%3EWhy%20would%20this%20formula%20work%20in%20some%20cells%20and%20not%20others%3F%20Please%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806491%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20formula%20is%20working%20in%20some%20cells%20and%20not%20others.%20It%20is%20driving%20me%20crazy.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(G3%26gt%3B%3DG6%2C%22PASS%22%2C%22FAIL)%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(8).png%22%20style%3D%22width%3A%20756px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228389i758768AF4F64787C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(8).png%22%20alt%3D%22Screenshot%20(8).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1806491%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806815%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20would%20this%20formula%20work%20in%20some%20cells%20and%20not%20others%3F%20Please%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806815%22%20slang%3D%22en-US%22%3E%3CP%3EHard%20to%20say%2C%20since%20we%20have%20no%20idea%20which%20is%20row%203%20and%206.%20(Klunk!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20when%20you%20post%20formulas%2C%20be%20sure%20to%20copy%20from%20the%20Formula%20Bar%20(*)%20to%20avoid%20silly%20typos%20like%20%22FAIL%20%3CEM%3E(sic)%3C%2FEM%3E%20(missing%20right-parenthesis)%20instead%20of%20%22FAIL%22%3B%20and%20to%20avoid%20inadvertently%20fixing%20whatever%20the%20problem%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20it%20is%20essential%20to%20provide%20the%20formulas%2C%20at%20least%20in%20referenced%20cells%20(e.g.%20G3%20and%20G6)%3B%20and%20to%20provide%20the%20exact%20values%20(15%20significant%20digits)%2C%20not%20just%20the%20displayed%20(formatted)%20values.%26nbsp%3B%20Even%20better%3A%26nbsp%3B%20attach%20an%20example%20Excel%20file%20(redacted)%20that%20demonstrates%20the%20problems.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETaking%20a%20wild%20guess....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20row%203%20has%20the%20values%20displayed%20as%200.0300%20(D3)%2C%203.9800%20(E3)%2C%203.6000%20(F3)%2C%200.0416%20(G3)%20etc%3B%20and%20row%206%20has%20the%20values%20displayed%20as%20-0.2000%20(D6)%2C%203.9800%20(E6)%2C%203.6000%20(F6)%2C%200.0416%20(G6)%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20expect%20G3%3DG6%20is%20true%20(%22PASS%22)%20based%20on%20appearance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20since%20the%20formula%20is%20returning%20%22FAIL%22%2C%20obviously%20G3%3CG6%20is%3D%22%22%20true.%3D%22%22%3E%3C%2FG6%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20if%20you%20format%20each%20cell%20with%2016%20decimal%20places%20to%20display%2015%20significant%20digits%2C%20is%20G6%20really%20something%20like%200.04155...%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20if%20they%20truly%20look%20identical%20to%2015%20significant%20digits%2C%20it%20is%20possible%20that%20there%20is%20an%20infinitesimal%20difference.%26nbsp%3B%20In%20other%20words%2C%20%3DSUM(G3%2C-G6)%20might%20be%20a%20number%20of%20the%20form%20-6.98E-18%20when%20formatted%20as%20General%20or%20Scientific.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Do%20not%20write%20%3DG3-G6.%26nbsp%3B%20Excel%20might%20change%20the%20result%20to%20exact%20zero%20because%20the%20values%20are%20%22close%20enough%22.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20either%20case%2C%20the%20remedy%20might%20be%20to%20explicitly%20round%20the%20calculations%2C%20if%20any%2C%20in%20G3%20and%20G6%20to%205%20decimal%20places%2C%20the%20precision%20that%20you%20display.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20only%20you%20can%20decide%20if%20that's%20appropriate%20and%20what%20precision%20to%20round%20to%2C%20based%20on%20your%20intentions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806838%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20would%20this%20formula%20work%20in%20some%20cells%20and%20not%20others%3F%20Please%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3BThank%20you%20for%20taking%20the%20time%20to%20help%20me.%20It%20was%20a%20rounding%20issue.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This formula is working in some cells and not others. It is driving me crazy. 

=IF(G3>=G6,"PASS","FAIL)Screenshot (8).png

2 Replies
Highlighted
Best Response confirmed by mduemmel (New Contributor)
Solution

 

Hard to say, since we have no idea which is row 3 and 6. (Klunk!)

 

Also, when you post formulas, be sure to copy from the Formula Bar to avoid silly typos like "FAIL (sic) (missing right-parenthesis) instead of "FAIL"; and to avoid inadvertently fixing whatever the problem is.

 

Finally, it is essential to provide the formulas, at least in referenced cells (e.g. G3 and G6); and to provide the exact values (15 significant digits), not just the displayed (formatted) values.  Even better:  attach an example Excel file (redacted) that demonstrates the problems.

 

Taking a wild guess....

 

Perhaps row 3 has the values displayed as 0.0300 (D3), 3.9800 (E3), 3.6000 (F3), 0.0416 (G3) etc; and row 6 has the values displayed as -0.2000 (D6), 3.9800 (E6), 3.6000 (F6), 0.0416 (G6) etc.

 

You might expect G3=G6 is true ("PASS") based on appearance.

 

But since the formula is returning "FAIL", obviously G3<G6 is true.  There can be many reasons.

 

First, if you format each cell with 16 decimal places to display 15 significant digits, is G6 really something like 0.04164... ?

 

Second, if they truly look identical to 15 significant digits, it is possible that there is an infinitesimal difference.  In other words, =SUM(G3,-G6) might be a number of the form -6.94E-18 when formatted as General or Scientific.

 

(Do not write =G3-G6.  Excel might change the result to exact zero because the values are "close enough".)

 

In either case, the remedy might be to explicitly round the any calculations in G3 and G6 to 5 decimal places, the precision that you display.

 

But only you can decide if that's appropriate and what precision to round to, based on your intentions.

Highlighted

@Joe User Thank you for taking the time to help me. It was a rounding issue. Thanks!