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

Jurassic_Sparks

New Member
Joined
Apr 9, 2014
Messages
2
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.

 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows

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.)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,201
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top