Jonny's Excel query no. 234535

[Jonny2621]

OK Another day, another Excel query

In my spreadsheet I assign a cell for point value for the next bet, ie 2% of last bank value. I format that and the stake cell to 0 decimal places to make it easier to record bets.

In my P&L coloumn I have the following formula

IF(E158=1,H158*0.96*(D158),-H158)

E158 is the results column where i record 1 for a win, 0 for a loss
H158 is the stake column
D158 is the decimal odds


Todays stake is £15.74 formatted to £16 in column H158
If i record the bet as a loss it now shows a P&L loss of -£15.74 rather than -£16 as it did previously and if it is a win it records the profit based on the bet of £15.74 not £16 :doh

Any ideas chaps ?

[mathare]

First thing to check would be the cell formatting and make sure it is set to 0 decimal places in all rows

Next thing is where is the value displaying as £15.74 - in the cell itself or in the white formula/result bar at the top of the screen?

All cell formatting does is change the display format - it doesn't actually change any of the data. So even if you format £15.74 to 0 decimal places so that it appears as £16 in the cell Excel still treats the value as £15.74 so if you do any calculations on that cell Excel will use the actual value (£15.74) and not the displayed value (£16).

[Jonny2621]

First thing to check would be the cell formatting and make sure it is set to 0 decimal places in all rows

Next thing is where is the value displaying as £15.74 - in the cell itself or in the white formula/result bar at the top of the screen?

All cell formatting does is change the display format - it doesn't actually change any of the data. So even if you format £15.74 to 0 decimal places so that it appears as £16 in the cell Excel still treats the value as £15.74 so if you do any calculations on that cell Excel will use the actual value (£15.74) and not the displayed value (£16).

In the white formula bar the formula for the P&L cell shows, in the cell itself it shows the 15.74

Previously whatever was shown in the point value cell was treated as the bet. I still have other sheets where this still happens.

I will try to append the file

[Jonny2621]

Blue rated file shows the bets as figures to 0 decimal places OK, the Ebooks file doesnt.

Hence my query...:doh

[MattR]

Jonny, can you not just stick an INT into the formula, so it becomes..

IF(E158=1,INT(H158*0.96*(D158)),-H158)


Also, are you forgetting to take off 1 from the decimal odds or have you already done that by recording it as one less in the odds column?

[Jonny2621]

I always take 1 off the decimal odds Matt, just prefer to do that myself.

[TheOldhamWhisper]

I'd go along with the Integer route too:


IF(E158=1,INT(H158*0.96*(D158)+0.5),INT(-H158-0.5))

Using +/- 0.5 in the formula will ensure that the figure is rounded up or down correctly.

[mathare]

Using Round([number],0) will round it to zero decimal places using traditional rounding methods (>=0.5 rounds up, else it rounds down)

[Jonny2621]

thanks guys, rep given where i am allowed to :)