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
Highlighted
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.

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
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies