• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

NOW( ), timestamps and enabling iterations

D

DashboardNovice

Member
I am reading the following page about timestamps and the NOW ( )
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

Case #1. If I use the NOW() by itself, pressing F9 will recalculate the time. This is true whether or not I click "enable iterations."

Case #2. When I paste the formula =IF(C3<>"",IF(B3="",NOW(),B3),"") in cell B3, if enable iterations is checked the timestamp does appear after I start typing in column C. This is good, but I still don't understand why pressing F9 at this time will not update the timestamp. Somehow F9 is blocked when you enable iterations. Or is it blocked because the NOW () is part of a circular reference?

Why does F9 work in case #1, but not case #2?
 
Hi ,

I think there is some confusion regarding the part played by F9 and interations , when actually it is neither of these.

Excel evaluates the relevant parts of the IF statement only on an as-needed basis ; thus , when you put in the formula :

=IF(C3<>"",IF(B3="",NOW(),B3),"")

in B3 , if C3 is blank , Excel does not display any message regarding circular references.

The message appears only when you put in some value in C3.

The point is that Excel does not evaluate those parts of the IF statement that are not relevant to the outcome.

You can see this if you click the Evaluate Formula button , and go through the evaluation steps. Excel starts from the left , and evaluates C3 to see if it is blank or non-blank. If it is blank , Excel does not evaluate the second IF at all ; it immediately goes to the action to be taken if FALSE part , and puts a blank in B3.

The moment you enter some value in C3 , the circular reference message immediately appears because Excel now evaluates the second IF statement , and finds a circular reference there.

When you put in a value of iterations , the circular reference goes away , since Excel now knows what is to be done in case of circular references viz. iterate the specified number of times , and output the result.

The first time through the second IF statement , Excel finds B3 is blank , and so puts in the current time ( NOW() ) in it ; thereafter , since B3 is not blank , the action to be taken if blank will never be executed ; pressing F9 will never recalculate the value entered in B3.

If you want a better way to visualize this , enter two formulae as follows : in B1 , enter the formula =NOW(). In B3 , enter the formula :

=IF(C3<>"",IF(B3="",B1,B3),"")

Now , use the Evaluate Formula button to step through the evaluation process ; as long as C3 is blank , B3 is blank ; the moment you enter a value in C3 , B3 gets the current time , which is the same as what is in B1.

After some time , if you click the Evaluate Formula button and step through the evaluation process , you will see the following :

1. Excel checks to see whether C3 is not blank.
2. It finds C3 is not blank.
3. It checks to see whether B3 is blank ; the value that it uses for B3 is the static value of time which was put in there earlier ! This means that B3 does not contain the function NOW() , as B1 does ; instead , it contains the value of time when the formula was evaluated.
4. It finds B3 is not blank.
5. It executes the action to be taken if FALSE part , and leaves B3 as it is i.e. it never evaluates the NOW() function in the action to be taken if TRUE part.

Narayan
 
You must log in or register to reply here.
Back
Top