0

I'm struggling to solve a problem with my Excel document so I'm hoping that someone more experienced can help?

I have a cell that updates my account balance after each trade is recorded. I then have in my ROW for each trade the % P&L based on the account balance at the time the row is completed - the problem is that the account balance cell is dynamic so as that changes then so does the individual trade P&L % (win/loss) in each row? I have tried to use the $h$1 cell lock but this is not working?

I started with a £5k balance so what I need to achieve is the % P or L for that particular trade at the time the trade was closed; currently it keeps changing as the account balance changes which is no use for recording purposes?

Can any one help? Thank

Improve this question
2
  • 3
    Welcome to superuser. Can you give a screenshot or a short example XLS uploaded somewhere? That would make it easier to help.
    – nixda
    Feb 6, 2013 at 20:19
  • You’re probably going to need to use VBA for this. P.S. It’s often helpful to specify what version of Excel you are using.
    – Scott - Слава Україні
    Feb 7, 2013 at 4:14

1 Answer 1

Reset to default
0

This is possible, but is not 100% secure. I suggets using dedicated accounting software for this.

In excel you are limited to making a calculation only update when you manually tell it to update.

From the top menu select Formulas then under Calculation Options select Manual...

enter image description here

You need to be aware that this will NOT prevent the calculation from happening again but it will not happen until someone tells it to calculate again.

Improve this answer
2
  • Many thanks Kevin - I'll give that a try! Is there a way of deleting the formula once it has activated and replacing the cell value with the result? That way once calculated it wouldn't change?
    – Danny
    Feb 7, 2013 at 9:56
  • That is very simple, simply copy and "paste value" on top of the cell. You can right click to get the option to "paste value"
    – Kevin Anthony Oppegaard Rose
    Feb 7, 2013 at 10:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .