Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tasi
 
Posts: n/a
Default Applying formula to only NON-EMPTY cells in range

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
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
Tasi
 
Posts: n/a
Default

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




  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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






  #5   Report Post  
Tasi
 
Posts: n/a
Default

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








  #6   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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


"Tasi" wrote in message
...
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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 10:07 PM
Getting rid of empty cells Jambruins Excel Discussion (Misc queries) 2 February 22nd 05 05:38 PM
put formula results into a different cell if it is empty PutFormula Excel Worksheet Functions 2 February 11th 05 04:31 AM
empty variable range S1 Excel Discussion (Misc queries) 5 January 28th 05 03:33 PM
How do I create formula to count numbers in a range of cells? EmilyJ Excel Worksheet Functions 1 December 8th 04 06:24 AM


All times are GMT +1. The time now is 09:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"