Creating a column formula in excel, changing only

问题:

I have a very basic excel file for looking at the cost of shares and calculating a profit/loss %.

I have the initial purchase price in cell E3 and I have the current share price in F3. I have calculated the percentage profit/loss in G3 by the following formula

=(F3/E3)*100 - 100

What I now want is to be able to apply this formula to the whole G column as I enter a new share price into the F column, it will use E3 as a constant in the formula to calculate daily profit/loss. So the new formula I want is effectively;

=(Fi/E3)*100 - 100 Where Fi = F3, F4, F5, F6 and so on...

I have tried dragging the cell down to extend the formula, which works to an extent but it does not keep E3 constant so I get a divide by zero error.

Any suggestions? Thanks

回答1:

Start with =(F3/E$3)*100 - 100. The $ is an absolute anchor that tells the formula not to change the 3 in E$3 when filled down.

If there is no value in column F, you can have the result returned as a zero-length string (e.g. "") which will make the cell in column G that holds the formula look blank.

=IF(LEN(F3), (F3/E$3)*100 - 100, "")

回答2:

Use an absolute reference for E3 in the formula:

$e$3

This will lock the reference if you drag the cell down.

回答3:

The other way around, if you want to lock the character:

=(F3/$E3)*100 - 100