Maddening reference error

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: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.

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" <none> wrote in message
news:efJtGcXXFHA.2348@TK2MSFTNGP14.phx.gbl...
> 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: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.
>
>

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: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?

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:ugtSH3fXFHA.2128@TK2MSFTNGP15.phx.gbl...
> 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" <none> wrote in message
> news:efJtGcXXFHA.2348@TK2MSFTNGP14.phx.gbl...
>> 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: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.
>>
>>
>
>