在Excel中创建列公式，仅更改一个变量 [英] Creating a column formula in excel, changing only one variable

问题描述

我有一个非常基本的excel文件，用于查看股票成本并计算利润/亏损百分比.

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

我在E3单元格中具有初始购买价格，而在F3中具有当前股价.我已经通过以下公式计算了G3中的损益百分比

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

=(F3/E3)*100 - 100

我现在想要的是在我向F列输入新股价时能够将此公式应用于整个G列，它将使用E3作为公式中的常数来计算每日损益.所以我想要的新公式是有效的；

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 Fi = F3，F4，F5，F6等...

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

我尝试将单元格向下拖动以扩展公式，该公式在一定程度上有效，但不能使E3保持恒定，因此我得到了除以零的误差.

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.

有什么建议吗?谢谢

推荐答案

以=(F3/E$3)*100 - 100开头. $ 是绝对锚，它告诉公式填写后不要更改 E $ 3 中的 3 .

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.

如果F列中没有值，则可以将结果作为零长度字符串(例如"")返回，这将使G列中包含该公式的单元格看起来空白.

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, "")

这篇关于在Excel中创建列公式，仅更改一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！