VBA ln function

J

James

I have this code that I have put together and it works fine as long as none
of my numbers are "0's" or blank cells.

'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
ActiveCell.FormulaR1C1 = _
"=EXP(INDEX(LINEST(LN(RC[-25]:RC[-15]),R1C22:R1C32),1,2))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
ActiveCell.FormulaR1C1 =
"=INDEX(LINEST(LN(RC[-26]:RC[-16]),R1C22:R1C32),1)"

In my spreadsheet I have values from $V$1:$AF$1 (always constant) which are
my X values, V3:AF3 are my Y values. The formula works great as long as
there are no 0's or blank cells, I have tried to work around this in terms of
HLOOKUP and VLOOKUP but it has become more troublesome than its worth.

Is there a way to code this so that if there is a blank value that it will
ignore the 0 or blank and the coresponding X value (from $V$1:$AF$1). The
reason the formula messes up when there is a 0 or blank cell is that you
cannot take the ln(0) or a blank cell.

Thank you for your help.
 
P

p45cal

James;604210 said:
I have this code that I have put together and it works fine as long a
none
of my numbers are "0's" or blank cells.

'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
ActiveCell.FormulaR1C1 = _
"=EXP(INDEX(LINEST(LN(RC[-25]:RC[-15]),R1C22:R1C32),1,2))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
ActiveCell.FormulaR1C1 =
"=INDEX(LINEST(LN(RC[-26]:RC[-16]),R1C22:R1C32),1)"

In my spreadsheet I have values from $V$1:$AF$1 (always constant) whic
are
my X values, V3:AF3 are my Y values. The formula works great as lon
as
there are no 0's or blank cells, I have tried to work around this i
terms of
HLOOKUP and VLOOKUP but it has become more troublesome than its worth.

Is there a way to code this so that if there is a blank value that i
will
ignore the 0 or blank and the coresponding X value (from $V$1:$AF$1).
The
reason the formula messes up when there is a 0 or blank cell is tha
you
cannot take the ln(0) or a blank cell.

Thank you for your help.
Click to expand...

Looks like your just looking for slope and intercept? LINEST, as you'v
found out, doesn't cope with blank values. SLOPE and INTERCEPT do.
Try:

Code
-------------------
'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
Selection.FormulaArray = _
"=EXP(INTERCEPT(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),LN(RC[-25]:RC[-15])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),R1C22:R1C32)))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
Selection.FormulaArray = _
"=SLOPE(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),LN(RC[-26]:RC[-16])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),R1C22:R1C32))"
 
J

James

This is excellent, can you help me with one more piece. I am not very good
with these arrays.

'Inserts R^2 Header and formula
Range("AW2").Select
ActiveCell.FormulaR1C1 = "R^2"
Range("AW3").Select
ActiveCell.FormulaR1C1 = "=RSQ(LN(RC[-27]:RC[-17]),R1C22:R1C32)"

Thank you



p45cal said:
James;604210 said:
I have this code that I have put together and it works fine as long as
none
of my numbers are "0's" or blank cells.

'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
ActiveCell.FormulaR1C1 = _
"=EXP(INDEX(LINEST(LN(RC[-25]:RC[-15]),R1C22:R1C32),1,2))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
ActiveCell.FormulaR1C1 =
"=INDEX(LINEST(LN(RC[-26]:RC[-16]),R1C22:R1C32),1)"

In my spreadsheet I have values from $V$1:$AF$1 (always constant) which
are
my X values, V3:AF3 are my Y values. The formula works great as long
as
there are no 0's or blank cells, I have tried to work around this in
terms of
HLOOKUP and VLOOKUP but it has become more troublesome than its worth.

Is there a way to code this so that if there is a blank value that it
will
ignore the 0 or blank and the coresponding X value (from $V$1:$AF$1).
The
reason the formula messes up when there is a 0 or blank cell is that
you
cannot take the ln(0) or a blank cell.

Thank you for your help.
Click to expand...

Looks like your just looking for slope and intercept? LINEST, as you've
found out, doesn't cope with blank values. SLOPE and INTERCEPT do.
Try:

Code:
--------------------
'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
Selection.FormulaArray = _
"=EXP(INTERCEPT(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),LN(RC[-25]:RC[-15])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),R1C22:R1C32)))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
Selection.FormulaArray = _
"=SLOPE(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),LN(RC[-26]:RC[-16])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),R1C22:R1C32))"

--------------------

Note that 0s (zeroes) in the x range will be counted as a valid values
of x but a value of 0 for the y range will be ignored.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=167566

Microsoft Office Help

.
Click to expand...
 
J

James

Nevermind I figured it out.

Thank you for the help

p45cal said:
James;604210 said:
I have this code that I have put together and it works fine as long as
none
of my numbers are "0's" or blank cells.

'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
ActiveCell.FormulaR1C1 = _
"=EXP(INDEX(LINEST(LN(RC[-25]:RC[-15]),R1C22:R1C32),1,2))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
ActiveCell.FormulaR1C1 =
"=INDEX(LINEST(LN(RC[-26]:RC[-16]),R1C22:R1C32),1)"

In my spreadsheet I have values from $V$1:$AF$1 (always constant) which
are
my X values, V3:AF3 are my Y values. The formula works great as long
as
there are no 0's or blank cells, I have tried to work around this in
terms of
HLOOKUP and VLOOKUP but it has become more troublesome than its worth.

Is there a way to code this so that if there is a blank value that it
will
ignore the 0 or blank and the coresponding X value (from $V$1:$AF$1).
The
reason the formula messes up when there is a 0 or blank cell is that
you
cannot take the ln(0) or a blank cell.

Thank you for your help.
Click to expand...

Looks like your just looking for slope and intercept? LINEST, as you've
found out, doesn't cope with blank values. SLOPE and INTERCEPT do.
Try:

Code:
--------------------
'Inserts A Header and formula
Range("AU2").Select
ActiveCell.FormulaR1C1 = "A"
Range("AU3").Select
Selection.FormulaArray = _
"=EXP(INTERCEPT(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),LN(RC[-25]:RC[-15])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-25]:RC[-15])),R1C22:R1C32)))"

'Inserts B Header and formula
Range("AV2").Select
ActiveCell.FormulaR1C1 = "B"
Range("AV3").Select
Selection.FormulaArray = _
"=SLOPE(IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),LN(RC[-26]:RC[-16])),IF(ISNUMBER(R1C22:R1C32)*ISNUMBER(LN(RC[-26]:RC[-16])),R1C22:R1C32))"

--------------------

Note that 0s (zeroes) in the x range will be counted as a valid values
of x but a value of 0 for the y range will be ignored.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=167566

Microsoft Office Help

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
Macro - very slow run in 2003 3
how to use Solver in Excel VBA 1
Checkbox linked to calcs 1
Data samples 3
"=TEXT(RC[-1],"MMM")" 4
need help in fine-tuning a formula that removes blank cells in a column range 3
macro to copy rows that contain a cell with a certain number 3

Top