Excel Help

[Nitsud]

Hi there

This is literally a continuation on this thread viewtopic.php?f=19&t=3946

But it isn't because I think this is really an excel problem I am having and am desperate for anyone's help.

I have attached the spreadsheet.

Basically I only trade football markets. Laying and then backing the draw. What I am looking for is to only pass my conditions to green all if the market hasn't been suspended for longer than 2 minutes. (Waiting for the odds to settle)

I have literally completed it and am just needing one small step.

So in the spreadsheet in cell J3. If the market gets suspended it creates a time stamp. If it isn't suspended it just fills it with 00:00:10. PLEASE NOTE: I have used Cell H2 as the suspended market. So if you type Suspended into cell H2 it will create the time stamp in J3.

Then I have used the Last Updated time in C3 to try check my differences. So in J2 I am trying to subtract the Last Updated and the Time stamp - to work out what the difference is. It seems to have a problem with this. I think its to do with the formatting.

Then in C2 I have put in my time difference that I want the sheet not to bet after the market gets suspended. So in cell J1 I have asked it to display NO BET or BET OK if the the difference in the the time is greater or less than our 2 minutes.

So it's just that one error I am having problems with is cell J2 when H2/market is Suspended.

Else cell F6 even has my final conditions to GREEN_ALL..... and this is all working. Just the problem with the times subtraction when suspended in J2.

Any any help would be appreciated!!!

PLEASE help meeee someoneeee!!!

Many thanks

[nigelk]

Hi Nistud

The formula in J2 is returning a number like: -40709.7490891204

You need to strip out the day/month/year in J3.

[Nitsud]

Hi Nistud

The formula in J2 is returning a number like: -40709.7490891204

You need to strip out the day/month/year in J3.

Thanks Nigel. I know you are the one who will be able to help me here.

On my pc the date month and year is stripped out. Only shows the time.

In J2 I only get ######### when the market is suspended?

[nigelk]

The Now() function returns the day/month/year/hh/mm

So although it's formatted (and what you see) is hh:mm, excel is still calculating the entire date.

You could try the following in J3, (but it does contain, as in your original formula, a circular reference).

=IF(H2="","00:00:10",IF(J3="00:00:10",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),J3))

However, you will still get the #### result if it is a negative time.

The best solution would be to convert all the times to decimal.


Cheers,Nigel.

[TheTub]

I've not read the whole thread so apologies if this is out of turn but when you see ####### this means the number is too big to be displayed fully.

Make the cell column wider to see the number.

[Nitsud]

Thanks Nigel.

I think I have found a better solution to the problem. Because the problem was we needed to market to settle after being suspended. So another way to check the market is settled is to set the overrounds to 115% for back and 85% for lay. Is this possible?

Many thanks
Dustin

[nigelk]

It should be easy to create a sheet with the back/lay overrounds, then just add them up and point your formulas to those cells.