SOLVED
Home

BUG ON FUNCTION IF ?

%3CLINGO-SUB%20id%3D%22lingo-sub-466483%22%20slang%3D%22en-US%22%3EBUG%20ON%20FUNCTION%20IF%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-466483%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20an%20issue%20on%20Excel%2C%20I%20think%20is%20a%20Bug.%20So%20follow%20this%20simple%20test%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlways%20Format%20Hour%20for%20All%20cell%3C%2FP%3E%3CP%3E1)%20Into%20Cell%20B4%20put%26nbsp%3B08%3A00%3A00%20%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Into%20Cell%20C4%20put%2012%3A00%3A00%3C%2FP%3E%3CP%3E3)%20Into%20Cell%20F4%20put%20formula%20%3DIF(SUM((C4-B4)%20%2B%20(E4-D4))%3D0%3B%22%22%3BSUM((C4-B4)%20%2B%20(E4-D4)))%26nbsp%3B%20-%26gt%3B%20(%20Result%26nbsp%3B04%3A00%3A00%20)%3C%2FP%3E%3CP%3E4)%20Into%20Cell%20G4%20put%26nbsp%3B04%3A00%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow....%20Into%20Cell%20H4%2C%3C%2FP%3E%3CP%3Eif%20you%20put%20G4-F4%20-%26gt%3B%26nbsp%3B(%20Result%26nbsp%3B00%3A00%3A00%20)%3C%2FP%3E%3CP%3E%3CSTRONG%3EBUT%3C%2FSTRONG%3E%20if%20you%20put%26nbsp%3B%26nbsp%3B%3DIF(F4%3D%22%22%3B%22%22%3BG4-F4)%20I%20give%20an%20error%20-%26gt%3B%26nbsp%3B(%20Result%26nbsp%3B%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20%3F%3F%3F%20It's%20a%20%3CSTRONG%3Ebug%3C%2FSTRONG%3E%20%3F%3F%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-466483%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467100%22%20slang%3D%22en-US%22%3ERe%3A%20BUG%20ON%20FUNCTION%20IF%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322578%22%20target%3D%22_blank%22%3E%40fcafra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DPZRI1IfStY0%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThat's%20a%20floating%20point%20error.%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DIF(F4%3D%22%22%3B%22%22%3BG4-F4%2B4-4)%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467493%22%20slang%3D%22en-US%22%3ERe%3A%20BUG%20ON%20FUNCTION%20IF%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2C%20so%20many%20floating%20point%20error%20examples%20here%20and%20they%20are%20coming.%20It%20looks%20like%20hot%20topic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469841%22%20slang%3D%22en-US%22%3ERe%3A%20BUG%20ON%20FUNCTION%20IF%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469841%22%20slang%3D%22en-US%22%3EGreat%20!!!%20Many%20many%20thanks%20...%20it's%20work%20!!!%20Thanks%20for%20your%20reply%2C%20have%20a%20good%20day%20Sir.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469848%22%20slang%3D%22en-US%22%3ERe%3A%20BUG%20ON%20FUNCTION%20IF%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469848%22%20slang%3D%22en-US%22%3EMany%20many%20thanks%20...%20I%20tryed%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E.%20Have%20a%20good%20day%20Sir.%3C%2FLINGO-BODY%3E
fcafra
New Contributor

Hi, I have an issue on Excel, I think is a Bug. So follow this simple test:

 

Always Format Hour for All cell

1) Into Cell B4 put 08:00:00  

2) Into Cell C4 put 12:00:00

3) Into Cell F4 put formula =IF(SUM((C4-B4) + (E4-D4))=0;"";SUM((C4-B4) + (E4-D4)))  -> ( Result 04:00:00 )

4) Into Cell G4 put 04:00:00

 

Now.... Into Cell H4,

if you put G4-F4 -> ( Result 00:00:00 )

BUT if you put  =IF(F4="";"";G4-F4) I give an error -> ( Result ################### )

 

Why ??? It's a bug ??? 

 

Thanks !

4 Replies
Solution

@fcafra 

That's a floating point error.

 

Try:

=IF(F4="";"";G4-F4+4-4)

@Detlef Lewin , so many floating point error examples here and they are coming. It looks like hot topic.

Highlighted
Great !!! Many many thanks ... it's work !!! Thanks for your reply, have a good day Sir.
Many many thanks ... I tryed @Detlef Lewin. Have a good day Sir.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies