Basic excel help

[filo]

I’ve recently set up a spreadsheet to log data for a laying system I’m paper trading at the moment, but as I’m a complete excel novice, I’ve had to give myself a crash course on the basics of how to use excel.

I’ve manage to sort out a formula to auto calculate the liability / potential profit of each individual bet and also a column that auto calculates a running balance based on whether I insert a positive number for profit or a negative number for a loss on each bet.


Pretty basic stuff to most I guess.


However, so far my sheet does not take into account commission deductions, so all figures are gross at the moment.


The formula in my running balance column G starts off =SUM(G2+F3)
How do I modify the formula so that it deducts a commission % on profit in column F?

Obviously it needs to recognise profit only and not deduct a % from any loss figures.

I’m guessing it requires the IF function somewhere, but am unsure exactly how to use it.


Thanks in advance!



f

[ktng]

Hi Filo,

There is probably a neater way to do it but this should work:-

IF(F3<0,G2+F3,G2+(F3*0.95))

This shows it for standard commission rate of 5% so obviously replace 0.95 with whatever you current commission rate is.

[filo]

Thanks for that ktng

Will give it a whirl and see how i go!

[filo]

Hi ktng,

I replaced the formula i had in cell G3 =SUM(G2+F3) with the formula IF(F3<0,G2+F3,G2+(F3*0.95)) but it hasn't worked and sheet now displays just the forumula in the cell, followed by '#VALUE' in every other cell in that column.

Have i done something wrong or.....?

[GeorgeUK]

put = in front of the formula
= tells excel that you are making a formula.

I this doesn't work, make sure the cell is not formatted as text

[ktng]

Yes sorry filo I had forgotton that you said that you were a complete novice and omitted the = before the formula

[filo]

Great stuff!

Thanks guys