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