I think I understand this, but it would help if you posted some actual numbers and the results you want.
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
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
21200 =LN(A6) 5 21205 =LN(A7) 21200 =LN(A8) 21210 =LN(A9) 21215 =LN(A10) 21220 21210 21200 21975 21985
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
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),"")
Last edited by kweaver; Sep 11th, 2019 at 08:28 AM.
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
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
Worksheet Formulas
Cell Formula B1 =IF(ROW()<=$C$1,LN(A1),"")
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
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
Worksheet Formulas
Cell 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
Worksheet Formulas
Cell Formula B1 =IFERROR(LN(INDEX(A:A,$C$1+ROW()-1)),"")
Thank You!!!
It works the way I was looking for.
You really got it.
Grazie
Ciao
M
Like this thread? Share it with others