Timer cell D2 IF statement when negative

[MiniBlueDragon]

Hi folks,

I have a trigger set up to fire a bet into the market based on cell D2 which is the countdown for the race. It all works perfectly well when the cell is displaying a positive time but as soon as the race is delayed the cell goes negative.

Have you figured out a way of identifying whether the cell isnegative using an IF statement? I've tried IF(D2<0,1,0) and MATCH("-",D2,0) in case it's formatted as text and I can find the - in the cell but have had no luck.

Any ideas?

[alrodopial]

IF(LEFT(C2,1)="-",......,......)

[MiniBlueDragon]

Perfect, thanks very much indeed!

[MiniBlueDragon]

OK so I'm trying to be a little more adventurous here and seem to have confused myself. The formula I'm trying to use should do the following:

- IF AC7 (current back odds) is blank, display blank.
- IF Hour in time cell is less than 1 AND if the minute is less than 1 AND if the seconds are equal to or less than 20 OR if the time cell is negative then set cell value to "GO" otherwise set to "WAIT"

Here's the code I have which for some reason fails:

Code: Select all

=IF(AC7="","",IF(OR(AND(HOUR($D$2)<1,MINUTE($D$2)<1,SECOND($D$2)<=20),LEFT($D$2,1)="-"),"GO","WAIT"))

Any ideas please?

[Captain Sensible]

- IF Hour in time cell is less than 1 AND if the minute is less than 1 AND if the seconds are equal to or less than 20 OR if the time cell is negative then

I'm assuming all that means if the time is less than 20 seconds to the scheduled off time.

I'm guessing your code is falling over once the time goes negative because BA seems to send that as text once it's less than 0 and you'd get a value error. A quick fix would be to catch the "-" in your first if statement i'e. something like

=IF(AND(AC7<>"",LEFT($D$2,1)="-"),"GO",IF(AND(AC7<>"",$D$2<=0.00023148),"GO","WAIT"))


0.0023148 is just excels numerical value for 20 seconds, check it first as I'm second guessing what you wanted and haven't checked it myself

[sjaak1943]

OK so I'm trying to be a little more adventurous here and seem to have confused myself. The formula I'm trying to use should do the following:

- IF AC7 (current back odds) is blank, display blank.
- IF Hour in time cell is less than 1 AND if the minute is less than 1 AND if the seconds are equal to or less than 20 OR if the time cell is negative then set cell value to "GO" otherwise set to "WAIT"

Here's the code I have which for some reason fails:

Code: Select all

=IF(AC7="","",IF(OR(AND(HOUR($D$2)<1,MINUTE($D$2)<1,SECOND($D$2)<=20),LEFT($D$2,1)="-"),"GO","WAIT"))

Any ideas please?

Put the following formula in a cell say W1

=IF(LEFT(D2)<>"-",(HOUR(D2)*3600)+(MINUTE(D2)*60)+SECOND(D2),-((HOUR(SUBSTITUTE(D2,"-",""))*3600)+(MINUTE(SUBSTITUTE(D2,"-",""))*60)+SECOND(SUBSTITUTE(D2,"-",""))))

What the formula does is that it counts down to the scheduled off, and the figure becomes negative when gone past the scheduled off.

=IF(AC7="","",IF(W1<=20,"GO","WAIT")

[MiniBlueDragon]

Thanks guys. Had a look at the second option seemed to be a little more suitable for my needs.

Lets me have a central cell which shows whether the race timer is less than 20 secs but also lets me use a simple formula to fill down through each race selection. Both good solutions though, many thanks!