How to raise power to a formula along column

**iamsevenup** · 03-06-2009, 03:29 AM

Say I have a formula 2*1.1^1 in A1, but I want it to be 2*1.1^2 in A2, 2*1.1^3 in A3 and so forth. It seems simply drag it down the column wont work. How do I write a macro for this?

**JBeaucaire** · 03-06-2009, 03:37 AM

Put this formula in row 1 and copy it down, it will do what you want:

=2*1.1^ROW()

**DonkeyOte** · 03-06-2009, 03:39 AM

I would advise ROWS over ROW()

=2*1.1^ROWS(A$1:A1)

**JBeaucaire** · 03-06-2009, 03:50 AM

Since you're determined to find improvement on every post I offer, at least on this one I'm interested in knowing why? Why is ROWS() better than ROW() in this application.

**DonkeyOte** · 03-06-2009, 04:06 AM

JB, I only post if I feel they can be improved which in your case is rarely - if you don't think that it's worth me pointing out possible improvements that's your prerogative obviously.... personally I learn from people illustrating to me how my formulae can be improved or if another method may be deemed preferable... hopefully the OP will find them useful even if you don't.

ROWS is not Volatile.... I *believe* ROW is (or was)... Regardless of Volatility ROWS is regarded as a more flexible approach in the long run as it will adapt as rows are inserted / removed without need for continual adjustment on the part of the user... eg if user inserts say a header row in row 1 ROWS will update accordingly (ROWS(A$1:A1) will become ROWS(A$2:A2) meaning resulting value will still generate correctly (ie row 2 to ^1 and A3 to ^2) whereas if ROW approach were used the user would have to remember that by adding rows they must revise their ROW formulae to incorporate the adjustment of -1 ... in A2: ROW() would generate to ^2 unless adjusted.

**iamsevenup** · 03-06-2009, 03:59 AM

Thank you both of you

**JBeaucaire** · 03-06-2009, 04:09 AM

Never knew any of that. Thanks for the input. I'll keep that in mind. Some things you do for so long it never occurs to you that it's a problem that can be avoided...like adding the -1 on Row(). I'll watch for ways to try it out.

Thanks again.

Iamsevenup, if that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]