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.

Great !!! Many many thanks ... it's work !!! Thanks for your reply, have a good day Sir.
Highlighted
Many many thanks ... I tryed @Detlef Lewin. Have a good day Sir.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
222 Replies