Price change percent function

[Bobsmilk]

I'm building a historical database in SQL

Having difficulties calculating BF SP / IP price drop as a percentage - I realise that this should be quite simple but when the SP is below 2, there are some odd results

Does anyone have the formula for this?

Spreadsheet attached.

[jimibt]

I'm building a historical database in SQL

Having difficulties calculating BF SP / IP price drop as a percentage - I realise that this should be quite simple but when the SP is below 2, there are some odd results

Does anyone have the formula for this?

Spreadsheet attached.

Bob, i think you'll be better served using book%, rather than odds. book% is calculated as 100/odds - i.e. 2 odds =50%, 5 odds = 20% etc, etc...

[weemac]

I use the following where U2 is the IP low and O2 is BFSP. It works for me but I can't vouch for it being the absolutely correct method.

=IF(U2<2,(U2-1)/(O2-1),U2/O2)

[Bobsmilk]

I'm building a historical database in SQL

Having difficulties calculating BF SP / IP price drop as a percentage - I realise that this should be quite simple but when the SP is below 2, there are some odd results

Does anyone have the formula for this?

Spreadsheet attached.

Bob, i think you'll be better served using book%, rather than odds. book% is calculated as 100/odds - i.e. 2 odds =50%, 5 odds = 20% etc, etc...

I use the following where U2 is the IP low and O2 is BFSP. It works for me but I can't vouch for it being the absolutely correct method.

=IF(U2<2,(U2-1)/(O2-1),U2/O2)

Thanks for your reccomendations. It seems that there isn't actually a correct answer since the tick increments are non lineal, it is always going to be out.

In the end I used:

IF(IPMin<=1.01, 0, IPMin/BFSP*100.0)

You have to invert the numbers when looking at DOBs etc but it does the job