Hello Peo,
Unfortunately I do not know how dynamic ranges work. Is there another way
for the formula to ignore blank cells? It seems to skip blank cells after
the last filled in cell, but if you hi-light emtry cells before your actual
range begins, then it returns o. Thanks,
-Tasi
"Peo Sjoblom" wrote:
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