What is this Excel formula doing?

Question

Can someone please explain in English wtf this formula is doing? I'm looking at someone else's work and have no idea.

=SUM(OFFSET(INDIRECT((ADDRESS(ROW(),21)),0,0,1)CurrentActualPeriod))

Answer 1

Sorry, without data I'm no better off than you are. I would suggest breaking each of the functions out into their own cell so that you can see what they are returning. This will allow you to make yourself some kind of call stack so you can see what is happening.

Just by looking at this I'm pretty sure it could be simplified substantially. In my years of Excel development, I don't recall ever having to resort to the Address function. Offset is pretty powerful and confusing when used correctly. :-)

Answer 2

Agreeing with jeffreymb, you can also use the Evaluate Formula function in Excel to step through the nested functions one at a time (if you have Excel 2007). Here is documentation and a screenshot on how that works:

http://office.microsoft.com/en-us/excel-help/evaluate-a-nested-formula-one-step-at-a-time-HP010066254.aspx

Answer 3

I use the ADDRESS and INDIRECT functions quite often.

Does the formula actually work? If it does, which version of Excel are you using? I can't get it to work in Excel 2003.

For me, the ADDRESS function has too few argument, and the ones there are wrong...; it should be (something like) ADDRESS(ROW(),21,1,1,1)

The CurrentActualPeriod probably refers to a named range, and should probably have a comma before it.

I can't do much more than that atm..

Answer 4

Assuming that this formula appears in D4, then:

= SUM(
      OFFSET(
             INDIRECT(
                 (
                   ADDRESS(
                      ROW(),21
                   )
                 ),0,0,1
             )
             CurrentActualPeriod
      )
  )

ROW() = 4

ADDRESS(4,21) = $U$4

INDIRECT($U$4) = take contents of $U$4, use as address, and get that cell's value

OFFSET(..., 0, 0, 1) = ?

The rest appears to be a syntax error, unless there's a missing comma before CurrentActualPeriod.

The double indirection sure is confusing. Build an address, get a cell address from there, use it for ANOTHER level of indirection... Ouch