ROW() within SUM() not working in named formula in Excel 97

[JMCC]

Excel is driving me crazy. I've been working on a formula in Excel 97 that ended up being pretty complex. To make it easier to read I started breaking it down into pieces using named formulas (Insert > Name > Define). Now I'm hitting a roadblock with Excel's handling of named formulas.

Basically, the ROW() function is not working when nested within the SUM() function in a named formula, and I have no idea why.

Here is my test data:

Excel Workbook
	A	B
1
2
3
4	2
5	3
6	4
7	5
8		???
...

This is a small part of my overall formula that illustrates the specific problem I'm posting about. I'm using the constants 4 and -1 here to simplify the test. In my actual formula those values are calculated dynamically.

I'll refer to my named formula as "Daily_total".

*** 1 ***

Daily_total:
=SUM( OFFSET( B8, ( 4 - ROW() ), -1, 4 ) )

Formula in B8:
=Daily_total

Result: #VALUE!


*** 2 ***

Daily_total:
=SUM( OFFSET( B8, ( 4 - ROW( B8 ) ), -1, 4 ) )

Formula in B8:
=Daily_total

Result: #VALUE!


*** 3 ***

Daily_total:
N/A

Formula in B8:
=SUM( OFFSET( B8, ( 4 - ROW() ), -1, 4 ) )

Result: 14


*** 3 ***

Daily_total:
=SUM( OFFSET( B8, ( 4 - 8 ), -1, 4 ) )

Formula in B8:
=Daily_total

Result: 14


*** 4 ***

Daily_total:
=OFFSET( B8, ( 4 - ROW() ), -1, 4 )

Formula in B8:
=SUM( Daily_total )

Result: 14

Why is this happening?

 

[Andrew Poulsom]

Try:

=SUM(OFFSET(B8,(4-MIN(ROW())),-1,4))

The ROW function returns an array like {8} which causes a problem when used in a named formula.

 

[JMCC]

Thanks a lot Andrew! That does indeed seem to have solved the problem. I don't completely get it, because I use ROW() in several places in the formula, and I only had to make the change you suggested in one place to get my formula to work. The ones I didn't have to change are like:

INDIRECT( ADDRESS( ( ROW() -1 ), COLUMN() ) )

Thank you, I don't think I ever would have figured that out. I'm still not clear on why it works differently in the named formula -- why the same formula put into a named formula or put directly in the cell produce different results, or why leaving SUM() out of the named formula and putting =SUM( Daily_total ) in the cell works.