SOLVED

formula error with specific values only

Copper Contributor

I'm getting the incorrect answer when the value in F15 is 2200 and the value in G15 is 2345. Instead of returning 1.75 it rounds up an additional 15 minutes to 2.0. It only seems to do it with those values, all others I try seem to work. Any ideas why?

 

=IFERROR(IF(AND(J15=FALSE,O15=FALSE),CEILING.MATH((TEXT(G15,"00\:00")-TEXT(F15, "00\:00")+(F15>G15))*24,15/60),0),0)

 

3 Replies
best response confirmed by harcorebosch (Copper Contributor)
Solution

@harcorebosch 

Looks like floating point error. This one

=IFERROR(IF(AND(J15=FALSE,O15=FALSE),CEILING.MATH((24*TEXT(G15,"00\:00")-24*TEXT(F15, "00\:00")+24*(F15>G15)),15/60),0),0)

works

Thank you very much, and congratulations on being smarter than chatgpt.

For my own learning, what do you mean by floating point error?
1 best response

Accepted Solutions
best response confirmed by harcorebosch (Copper Contributor)
Solution

@harcorebosch 

Looks like floating point error. This one

=IFERROR(IF(AND(J15=FALSE,O15=FALSE),CEILING.MATH((24*TEXT(G15,"00\:00")-24*TEXT(F15, "00\:00")+24*(F15>G15)),15/60),0),0)

works

View solution in original post