VBA ln function

[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.

 

[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

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.

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

.

 

[James]

Nevermind I figured it out.

Thank you for the help

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.

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

.