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
|