Setting a dynamic range in a formula

  • Thread starter Thread starter Phillycheese5
  • Start date Start date
P

Phillycheese5

Hi,
I have a column of numbers and I always want the following arra
formula to use the last 12 entries:
=(PRODUCT(1+D1:D12/100)-1)*100
Any suggestions?
Thanks,
Phillycheese
 
Assuming that Column D contains no blanks, try...

=(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

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

Setting dynamic range in a formula 9
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Hide rows in table based on 1 empty cel of the same row. 1
Excel VBA 1
How to set cell value to emptry if three other columns are empty too 1
Modify range in VBA 9
Formula Problem 3
Quick way to fill formulas? 2

Back
Top