In excel, say I am trying to find average of 5 cells, I can put a formula
=Average(C1:C5)
I would like to modify the formula such that
= Average(C1:CXXXX)
where XXXX comes from another cell.
Is there a way to achieve this?
Add a comment
|
2 Answers
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:
=AVERAGE(INDIRECT("C1:C"&D1))
If this is in vba, then you can use:
= Average(Range(Range("C1"),Range("C") & XXXX))
where XXXX is the row number, assuming that Average is a function that you have defined somewhere.
1 Comment
AMM
The INDIRECT function was exactly what I was looking for. I am not familiar with VBA. Will explore it.
Use the INDIRECT function. Indirect returns the reference by a string. INDIRECT("A1") returns the cell A1.
Your values are in the C column. Let's say that the XXXX is in cell D1. The formula becomes
=AVERAGE(C1:INDIRECT("C" & D1)).
