Need a quick nudge in the right direction

Copper Contributor

Hi All,

Ive spent 3 nights trying to solve a formula, and I need a pointer.

=B5*(($M$6+$M$11)+($M$9*SUM(ADDRESS((ROW()-$M$7),2,4):ADDRESS((ROW()),2,4))))

Formula returns #VALUE! result.

The error seems to arise in the bolded section above.

If I break each section down, eg, I insert =ADDRESS((ROW()-$M$7),2,4) into its own cell I get the correct result for this portion of the overall formula.  It gives the result of a cell reference (B10 in this example)

If I insert ADDRESS((ROW()),2,4)) into another cell it gives me the right result (B35 in this example).

When I add these two components together in a SUM formula it falls apart SUM(ADDRESS((ROW()-$M$7),2,4):ADDRESS((ROW()),2,4))

 

In the bold section I am trying to arrive at the result of SUM(B10:B35).

 

Cell formating is all general numbers

 

Where am I gong wrong?

3 Replies

Hi

 

ADDRESS() returns a text and not a reference. To convert a text into a reference use INDIRECT().

 

However, because INDIRECT() is a volatile function I would suggest:

SUM(INDEX(B:B,ROW()-$M$7):INDEX(B:B,ROW()))

 

Thanks mate, this looks much simpler than my efforts and it worked perfectly

I have a try like this

=SUM(OFFSET($B$1,ROW()-$M$7-1,0,$M$7+1,1))

Perhaps, it could be

=SUM(OFFSET($B$1,ROW(B1)-ROW($B$1),0,$M$7+1,1))