Maddening reference error

[Wowbagger]

Using Excel 2003 on an XP pro machine I created a 5 sheet workbook.

On (named) sheets 2-5 column A has a date, columns B and C has a number,
column D is equal to C-B for each row. Row 1 of all four sheets is a text
label.

On sheet one I created a summary with five rows (row one is text labels
only)

Column A has a text label, B is:

=SUMIF(~sheet~!C:C,">0",~sheet~!B:B)

column C is

=SUM(~sheet~!C:C)

column D is

=SUM(~sheet~!D)

column E is (one example)

=1-B3/ABS(C3)

column F

=AVERAGE(IF(~sheet~!B:B<>0, ~sheet~!B:B,""))

3 of the four rows in column F work. One column returns #VALUE!

When I look at the step by step evaluation it says
Reference: Summary!$F$4
Evaluation AVERAGE( "")

For the life of me I can't figure out what is going wrong here. All other
references to that sheet work, I've typed in the formula manually as well as
copied from one of the working cells then changed the name of the referenced
sheet.

I'm out of ideas - any suggestions would be greatly appreciated.

 

[Bernie Deitrick]

Your formula:

=AVERAGE(IF(~sheet~!B:B<>0, ~sheet~!B:B,""))

appears to be an array formula, which cannot accept full column references.
Also, my Excel didn't like the ~sheet~ name, and put it in single quotes.
Try array entering (using Ctrl-Shift-Enter) a formula like:

=AVERAGE(IF('~sheet~'!B1:B100<>0, '~sheet~'!B1:B100,""))

HTH,
Bernie
MS Excel MVP

 

[Wowbagger]

I was using "~sheet~" to represent the respective sheet names... kind of
like a wildcard, not the actual name.

This particular sheet is called "Northeast" - but if I change the sheet name
to anything else it still won't work.

On my summary sheet this particular sheet is referenced in row 4 - column A
is nothing but the text "Northeast", the formulas in each of the next
columns are as follows:

=SUMIF(Northeast!C:C,">0",Northeast!B:B)
=SUM(Northeast!C:C)
=SUM(Northeast!D)
=1-B4/ABS(C4)
=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))

The other rows have identical formula constructs with only the referenced
sheet name changed.

To further confuse me I did a little experimenting and discovered that the
formula works in some rows but not in others. I copied the formula and
pasted it into several other rows. Starting with row 6 this is how the
sheet now appears:

56.16666667
#VALUE!
56.16666667
56.16666667
#VALUE!
#VALUE!
56.16666667
56.16666667
56.16666667
56.16666667
56.16666667
#VALUE!

Why do some rows work and some rows don't?