Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: =product(indirect(address((row(j$35)+1-$ai20),column(j$35),true)):j$35)-1

    What is going on with this? i looked at each item in help but im not to smart, and to try to put them all togater gave me a headache

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Work from inside out:

    ROW(J$35)
    gives: 35 (row number)

    (ROW(J$35)+1-$AI20)
    gives row number , adds one to it, then subtracts the value in cell AI20

    ADDRESS((ROW(J$35)+1-$AI20),COLUMN(J$35),TRUE)
    gives the address of the cell in column J

    INDIRECT(ADDRESS((ROW(J$35)+1-$AI20),COLUMN(J$35),TRUE))
    gives the value in the cell above

    PRODUCT(INDIRECT(ADDRESS((ROW(J$35)+1-$AI20),COLUMN(J$35),TRUE)):J$35)
    multiplies that value by the value in cell J35

    PRODUCT(INDIRECT(ADDRESS((ROW(J$35)+1-$AI20),COLUMN(J$35),TRUE)):J$35)-1
    subtracts 1 from that total.

    So, for example if you have the following values in these cells:

    J15: 2
    AI20: 2
    J35: 10
    J34: 28

    Your final answer would be 279.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •