IF statement to highlight values crossing round and half round numbers

Hi all,

I am currently looking to automate a backtest for the foreign exchange market.

All currency pairs trade to four decimal points. I would like Excel to tell me when a value crosses a round number eg. 1.2800 and half round number eg. 1.2850.

I'm assuming the easiest way to do this would be by using an IF statement, but I have limited experience with formulas in Excel.

I have attached an example spreadsheet.

I hope this will help.

Conditional format cells B2:C5 where formula is true:

Formula:

=IF(LEN(B2)<=5;OR(MID(B2;5;1)="5";MID(B2;5;1)="0";LEN(B2)<=4))

Oops, semicolon is for my "middle east" country, please, replace with colon(

Oops, lost in translation! THANKS AGAIN DEAR ALI

If by round you mean the 3rd and 4th digits to the right of the decimal point are 0, then the test whether you've crossed a round number would be INT(100*open)<>INT(100*close), and the test whether you've crosses a half round number would be INT(200*open)<>INT(200*close). Something like

Formula:

=IF(INT(100*open)<>INT(100*close),"Price opens at "&TEXT(open,"0.0000")&" and crosses "&TEXT(ROUND(AVERAGE(open,close),2),"0.0000")&" to close at "&TEXT(close,"0.0000"),

IF(F8<>G8,"Price opens at "&TEXT(open,"0.0000")&" and crosses "&TEXT(ROUND(2*AVERAGE(open,close),2)/2,"0.0000")&" to close at "&TEXT(close,"0.0000"),""))