In excel, say I am trying to find average of 5 cells, I can put a formula
在excel，说我试图找到平均5个细胞，我可以把一个公式
=Average(C1:C5)
I would like to modify the formula such that
我想修改这样的公式
= Average(C1:CXXXX)
where XXXX comes from another cell.
其中XXXX来自另一个单元格。
Is there a way to achieve this?
有没有办法实现这个目标？
2 个解决方案
#1
4
You probably want to have a look at the INDIRECT function if you need it as a fomula in a cell - you can use it like this, assuming that D1 contains the row that you call XXXX in your question:
你可能想看一下INDIRECT函数，如果你需要它作为单元格中的公式 - 你可以像这样使用它，假设D1包含你在你的问题中调用XXXX的行：
=AVERAGE(INDIRECT("C1:C"&D1))
If this is in vba, then you can use:
如果这是在vba中，那么你可以使用：
= Average(Range(Range("C1"),Range("C") & XXXX))
where XXXX is the row number, assuming that Average is a function that you have defined somewhere.
其中XXXX是行号，假设Average是您在某处定义的函数。
#2
1
Use the INDIRECT function. Indirect returns the reference by a string. INDIRECT("A1") returns the cell A1.
使用INDIRECT功能。间接通过字符串返回引用。 INDIRECT（“A1”）返回单元格A1。
Your values are in the C column. Let's say that the XXXX is in cell D1. The formula becomes
您的值位于C列中。假设XXXX在单元格D1中。公式变成了
=AVERAGE(C1:INDIRECT("C" & D1)).
#1
4
You probably want to have a look at the INDIRECT function if you need it as a fomula in a cell - you can use it like this, assuming that D1 contains the row that you call XXXX in your question:
你可能想看一下INDIRECT函数，如果你需要它作为单元格中的公式 - 你可以像这样使用它，假设D1包含你在你的问题中调用XXXX的行：
=AVERAGE(INDIRECT("C1:C"&D1))
If this is in vba, then you can use:
如果这是在vba中，那么你可以使用：
= Average(Range(Range("C1"),Range("C") & XXXX))
where XXXX is the row number, assuming that Average is a function that you have defined somewhere.
其中XXXX是行号，假设Average是您在某处定义的函数。
#2
1
Use the INDIRECT function. Indirect returns the reference by a string. INDIRECT("A1") returns the cell A1.
使用INDIRECT功能。间接通过字符串返回引用。 INDIRECT（“A1”）返回单元格A1。
Your values are in the C column. Let's say that the XXXX is in cell D1. The formula becomes
您的值位于C列中。假设XXXX在单元格D1中。公式变成了
=AVERAGE(C1:INDIRECT("C" & D1)).