|
|
You can use a dynamic range
http://www.contextures.com/xlNames01.html#Dynamic
--
Regards,
Peo Sjoblom
"Tasi" wrote in message
...
Hello Bernie,
The 'cror' function is one that I created in Visual Basic but use in
Excel.
As far as B1000000000, that was an arbitrary number that I made up.
Basically what I was trying to convey is that I would like to have a huge
range (with most the cells being empty), and fill in the cells as the data
becomes available. Thus, I could command excel to perform my cror
function
of a massive range of cells (B1:B1000), but excel would know only to
calculate the function of the cells which actually have numbers/date
filled
into them (i.e. B1:B20). Is there a command that I can use to do this,
and
how would that formula be incorporated iinto my general formula:
=(1+((1+cror(B1:B1000))^(1/B16)-1))^12-1.
Thanks again Bernie,
-Tasi
"Bernie Deitrick" wrote:
Tasi,
Excel doesn't have a function CROR, and can't use B1:B10000000, since
Excel
only has 65536 rows. Are you using a different program?
HTH,
Bernie
MS Excel MVP
"Tasi" wrote in message
...
Hello,
I am trying to apply a formula I created to a range of cells. When I
select
the exact cells that I want the formula to calculate (i.e.
B1:B20-these
are
all cells where i have entered data), then the formula works great.
However,
it is too time comsuming for me to select the exact range of cells
everytime
I fill in a new cell, so it would be nice if I could pick a range
greater
than the one I am currently using (i.e. B1:B10000000-with everything
after
B20 being an empty cell), and have excel automatically know to stop
calculating at B20 because B21 is an empty cell. What command would i
have
to use to do this, and how would I incorporate it into my general
formula
which is listed below:
=(1+((1+cror(B1:B1000))^(1/B16)-1))^12-1.
Thanks in advance,
-Tasi
|