Nov 13 2018 11:23 PM
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?
Nov 13 2018 11:43 PM
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()))
Nov 14 2018 12:15 AM
Nov 14 2018 01:04 AM - edited Nov 14 2018 01:49 AM
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))