Want to "ignore" the FALSE result of an IF statement

**shawnh** · 02-09-2010, 05:21 PM

Hi all, here's kind of an interesting one I think. I have a formula in a cell which attempts to grab a value on another sheet that's dynamically changing in real-time (it's a live stock price changing in realtime via DDE). My objective is to grab this current live stock price when it is between the times of 10:30 AM and 5:00 PM, bt as *soon* as it goes past 5 PM, I want this formula cell to display the very *last* (ie.the one just before) value it had just before the time rolled over past 5 pm.

Here's my formula:

Please Login or Register  to view this content.

... as you can see, the "live" price I'm grabbing is Tickers!T12. This works fine during my specified times, but as soon as it goes past 5PM, it displays FALSE in my formula cell (presumably because I never specified a 'false' condition in my IF statement). Instead of it showing FALSE, I need it to show the very last live price it had *before* it rolled over past 5 PM. Maybe this is not possible?

Thanks!
Shawn

**shg** · 02-09-2010, 05:24 PM

You'd have to enable iteration to allow a circular reference. Me is the address of the cell in which the formula appears.

=IF(AND(NOW() >= "10:30:00" + 0, NOW() <= "17:00:00" + 0), Tickers!T12, Me)

**shawnh** · 02-09-2010, 05:35 PM

Thanks shg... how do I "enable iteration" ?

Thanks
Shawn

**shg** · 02-09-2010, 05:57 PM

Tools > Options > Calculation

**shawnh** · 02-09-2010, 05:58 PM

oh ok, thanks shg. Do I need to set "Maximum Iterations" or "Maximum Change" to anything in particular?

Thanks
Shawn

**shg** · 02-09-2010, 05:59 PM

One iteration is all you need for this.

It will mask any other circular references in the workbook, which are, in most cases, errors.

**shawnh** · 02-09-2010, 07:03 PM

thanks shg. What's the significance of the "+ 0" in the formula?

Also, shouldn't I put back in my TEXT(NOW(),"hh:mm:ss") ? Just the NOW() function alone returns the date too, which I don't want.

Thanks
Shawn

**shg** · 02-09-2010, 07:09 PM

1. +0 converts the numeric time string to a number.

2. Yes, the formula should be

=IF(AND(NOW() - TODAY()>= "10:30:00" + 0, NOW() - TODAY() <= "17:00:00" + 0), Tickers!T12, Me)

TEXT(), like all formatting, is a relatively heavyweight function.

If you're going to use the expression a lot, I'd put =NOW() - TODAY() in a separate cell and reference as needed.

EDIT: Another way:

=IF(MATCH(MOD(NOW(), 1), {0,"10:30","17:00"}+0) = 2, Tickers!T12, Me)

**shawnh** · 02-09-2010, 07:16 PM

Awesome, that looks like it's going to do the trick shg, but I won't know for sure till the markets open back up tomorrow morning. I daresay it's gonna work though. You are the man.

Thanks
Shawn

**shawnh** · 02-16-2010, 07:02 PM

it did work great shg, thanks again.

Cheers
Shawn

Want to "ignore" the FALSE result of an IF statement

LinkBack

Thread Tools

Rate This Thread

Display

Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Re: Want to "ignore" the FALSE result of an IF statement

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions