IF formula doesn't always work when using calculated values for logical_test

[Jurassic_Sparks]

Hello

I've searched a lot for the answer to my problem but with no success so far; please accept my apologies if something like this has been posted before.

I'm not entirely sure how to word this question, so it might be easier to give you an example from my spreadsheet.

I have a very simple worksheet that calculates the finish time of a movie based on a user-inputted start time, and running time. Using a simple IF formula and conditional formatting, it displays the start and finish time of each movie along with the colour-coded duration, in a sort of bar chart sort of way.

The problem that I'm having is that the formula/conditional formatting doesn't seem to work for about 20 given time-slots. Ignoring the conditional formatting for now (it uses the same IF formula), the example below shows the problem that I am having getting the finish times to be displayed.



Cell A2 is the Running Time
enter the number of minutes, e.g. 80

Cell B2 is the Start Time
enter the start time e.g. 19:00

Cells C1, D1 and E1 are the example Time-Slots

Cell C1 enter 20:20
Cell D1 enter 20:25
Cell E1 enter 20:30

Cell F2 converts the Running Time into a decimal
enter the formula =A2/1440

Cell G2 is the Finish Time and is calculated by adding the decimalised Running Time to the Start Time
enter the formula = F2+B2

Cells C2, D2 and E2 each check if the Finish Time is the same as the Time-Slot. If it is the same, the Finish Time is displayed. If it isn't the same, the cell remains blank

Cell C2 enter the formula =IF(G2=C1,C1,"")
Cell D2 enter the formula =IF(G2=D1,D1,"")
Cell E2 enter the formula =IF(G2=E1,E1,"")



Using the above example, you should find that 20:20 is correctly displayed in cell C2, below the corresponding 20:20 Time-Slot.

Now change the Running Time in cell A2 to 90 minutes. You will find that 20:30 is correctly displayed in cell E2.

However, change the Running Time to 85 minutes. No Finish Time appears; D2 remains blank.

My spreadsheet is set up in 5-minute increments from 10am to midnight and the following time-slots also do not appear to work:

16:20 / 16:35 / 17:05 / 17:25 / 17:50 / 18:10 / 18:35 / 18:55 / 19:20 / 19:40 / 20:05 / 20:25 / 20:50 / 21:10 / 21:35 / 21:55 / 22:20 / 22:40 / 23:05 / 23:25 / 23:50

Every other time-slot does, just not these.

If I manually type the finish time over the formula in cell G2, everything works properly, but as soon as I put the formula back in it stops working for the above time-slots.

I have checked every single cell to make sure that they are formatted correctly, and as I say, the formula works exactly how I want it to, except for the above time-slots. I am at a loss to think why this group of seemingly random times would stop the formula from working.

Can anyone think why this might be occurring or maybe there is a simple workaround that someone can think of?

I am using Excel 2010 on Windows 7.

Thanks in advance for any help you might be able to offer.

 

[Special-K99]

Its because when A2 is set to 85 mins

G2 is 0.850694
but D2 is 0.850694444444445
select D2 and view its actual value in the formula bar

Try using a ROUNDing formula
Same goes for the other time values on row 1

 

[joeu2004]

Cell F2 converts the Running Time into a decimal
enter the formula =A2/1440
Cell G2 is the Finish Time and is calculated by adding the decimalised Running Time to the Start Time
enter the formula = F2+B2
[....]
My spreadsheet is set up in 5-minute increments from 10am to midnight and the following time-slots also do not appear to work:
16:20 / 16:35 / 17:05 / 17:25 / 17:50 / 18:10 / 18:35 / 18:55 / 19:20 / 19:40 / 20:05 / 20:25 / 20:50 / 21:10 / 21:35 / 21:55 / 22:20 / 22:40 / 23:05 / 23:25 / 23:50

Use the following formulas instead:

A2: =--TEXT(A2/1440,"hh:mm")
G2: =--TEXT(F2+B2,"hh:mm")
D1: =--TEXT(C1+TIME(0,5,0),"hh:mm")

if C1 is 10:00.

--TEXT(...,"hh:mm") effectively rounds time to the minute, producing the same internal representation as the constant.

The double-negate (--) arithmetic converts text to numeric. Any equivalent arithmetic would do the same; for example, multiply by one or add zero.

The problem is: time is represented as a decimal fraction. Often, arithmetic with decimal fractions results in infinitesimal differences because of the way that numbers are represented internally (64-bit binary floating-point). Sometimes, the difference is significant enough to be apparent when formatted as Number with 15 significant digits (Excel's arbitrary formatting limit). Sometimes, they are not.

For example, the constant 20:25 in D1 is exactly 0.850694444444444,53079512413751217536628246307373046875.

But the calculated time 20:25 in G2 is exactly 0.850694444444444,4197728216749965213239192962646484375.

I use comma to demarcate 15 significant digits. In this case, the difference is only 1.11E-16. But by coincidence, the constant 20:25 is displayed as 0.850694444444445 because the 16th significant digit is rounded (usually).

In contrast, consider an example where A2 is 126 and D1 is 21:06. In that case, D1 and G2 differ by the same 1.11E-16, to wit: D1 is exactly 0.879166666666666,76288599546751356683671474456787109375, and G2 is exactly 0.879166666666666,6518636930049979127943515777587890625.

But G2=D1 returns TRUE(!) because of a heuristic in Excel that treats two values as equal if they are "close enough" in some circumstances. Note that D1 and G2 appear to be the same when formatted to 15 significant digits.

In contrast, G2-D1=0 returns FALSE because the two values are truly different.

(Obviously, the "close enough" heuristic is implemented and applied inconsistently.)

 

[Jurassic_Sparks]

Thank you both very much - that's been bugging me for ages! I go to bed a happy man