Macro and formula with variable

[maz71]

Hi, I wish I could have your help to solve what is a "big problem" for me in VBA.
Let me try to explain.
I have in column A a series of number, no blank cells. This is a dynamic serie, adding new data every day.
In cell C1 I have a variable, a number that I choose.
How can I get a formula in column B, in every cell starting from B1 to the row that depends on the number I set in C1 (call it B#), where in B# is the LN (natural logaritm) of the last value of column A. In the previous cell there will be the LN of the second to last value of column A, and so on.
Thank you
Ciao a tutti.
M

 

[kweaver]

I think I understand this, but it would help if you posted some actual numbers and the results you want.

 

[maz71]

I think I understand this, but it would help if you posted some actual numbers and the results you want.

Hi kweaver,
first of all I want to thank you. Please find below an easy sample. The target is to automate the file so that, changing cell C1 (5 in this example), I don't have to do all the calculations manually.
Ciao
M
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]21200[/TD]
[TD]=LN(A6)[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]21205[/TD]
[TD]=LN(A7)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21200[/TD]
[TD]=LN(A8)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21210[/TD]
[TD]=LN(A9)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21215[/TD]
[TD]=LN(A10)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21210[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21975[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21985[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

Is 21200 in A6, etc. or does 21200 start in A1?

Where does the 5 come from? It's not the natural log of 21200 nor 21220

 

[kweaver]

Does the 5 mean you want 5 natural logs calculated starting at A6?

If so, put this in B6 and fill down: =IF((ROW()-5)<=$C$6,LN(A6),"")

 

[maz71]

Does the 5 mean you want 5 natural logs calculated starting at A6?

If so, put this in B6 and fill down: =IF((ROW()-5)<=$C$6,LN(A6),"")

Hi kweaver, in the example above every cell starts at row 1. The problem is that column A is dynamic: I have new data to add every day. So the formula should find the very last data of column A and starting at the row I have set in C1 (5 in the example, but it could be 20... 100... ect...) must fill all the rows above (so if it start at row 5 with the LN of the very last value in col A, row 4 has the LN of the second to last value of col A and so on...).
Thank you
Ciao
M

 

[kweaver]

If this is B1: =IF(ROW()<=$C$1,LN(A1),"")
And the 5 (or whatever number) is in C1, filling down the B1 formula will create as many LN instructions as C1 indicated.

Excel 2010
	A	B	C
1	21200	9.96175646	6
2	21205	9.96199228
3	21200	9.96175646
4	21210	9.96222805
5	21215	9.96246376
6	21220	9.96269941
7	21210
8	21200
9	21975
10	21985
Sheet25

Cell Formulas
Range	Formula
B1	=IF(ROW()<=$C$1,LN(A1),"")

 

[maz71]

If this is B1: =IF(ROW()<=$C$1,LN(A1),"")
And the 5 (or whatever number) is in C1, filling down the B1 formula will create as many LN instructions as C1 indicated.

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]21200[/TD]
[TD="align: right"]9.96175646[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]21205[/TD]
[TD="align: right"]9.96199228[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]21200[/TD]
[TD="align: right"]9.96175646[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]21210[/TD]
[TD="align: right"]9.96222805[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21215[/TD]
[TD="align: right"]9.96246376[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]21220[/TD]
[TD="align: right"]9.96269941[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]21210[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]21200[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]21975[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]21985[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(ROW()<=$C$1,LN(A1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Kweaver, that doesn't work because in B1 the LN is not the one of the value in A1 (21200 in your sheet) but must be of row 5 (21215). In cell B6 the LN of the last row of col. A (21985), in B5 of 21975, in B4 of 21200, and so on. As I wrote the problem are: 1) I add new data every day to col. A; 2) I have to change the value in C1.
I really appreciate your hint.
Thank you,
Ciao
M

 

[kweaver]

When C1 is a 5, you want the value in B1 to be the natural log of the value in A5, then the LN(A6), etc. through until there's no more data in column A??
So, if there were 11 rows in column A, B7 would be LN(A11) ? I hope I got it now.

Kevin

Excel 2010
	A	B	C
1	21200	9.962463758	5
2	21205	9.962699412
3	21200	9.962228048
4	21210	9.961756461
5	21215	9.997660723
6	21220	9.998115682
7	21210
8	21200
9	21975
10	21985
11
LN

Cell Formulas
Range	Formula
B1	=IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")

AND

Excel 2010
	A	B	C
1	21200	9.962463758	5
2	21205	9.962699412
3	21200	9.962228048
4	21210	9.961756461
5	21215	9.997660723
6	21220	9.998115682
7	21210	9.952515784
8	21200
9	21975
10	21985
11	21005
LN

Cell Formulas
Range	Formula
B1	=IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")

 

[maz71]

Thank You!!!
It works the way I was looking for.
You really got it.
Grazie
Ciao
M