Applying formula to only NON-EMPTY cells in range

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Peo Sjoblom]

You can use a dynamic range

http://www.contextures.com/xlNames01.html#Dynamic

 

[Guest]

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

 

[Bernie Deitrick]

Tasi,

Since you are using a custom UDF, you could simply trim the range within
your function, along the lines of:

Function CROR(myRange As Range) As String
Dim myrange2 As Range
Set myrange2 = Range(myRange(1), myRange(1).End(xlDown))

CROR = myrange2.Address
End Function

HTH,
Bernie
MS Excel MVP