Formula works in some cells, doesn't in other

[Wowbagger]

I have a multi-sheet 2003 workbook.

Sheet 1 is a summary that displays data from the other 4 sheets, the name of
one of which is "Northeast" (though experimentation proves that it doesn't
matter what the sheet is named)

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 (without the B: C: D: E: and F

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

The other rows have identical formula constructs with only the referenced
sheet name changed and all work perfectly.

As is F4 displays "#VALUE". I have verified that the formula is exactly as
it should be and aside from the reference to that particular worksheet is
identical to the formula in column F in the other rows.

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!

To be very clear, in F6 the formula

=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
results in 56.16666667

In F7 the formula
=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
results in #VALUE!

Can anybody shed some light on the situation? Why does the result of a
formula depend on which cell it is in?

[Dave Peterson]

The formula in F4:
=AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
is an array formula (you used ctrl-shift-enter instead of just entering it into
the cell)

But array formulas don't work on whole columns:
=AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))

Should work ok. (But I'd use a range that was big enough--but not too big.)

Wowbagger wrote:
>
> I have a multi-sheet 2003 workbook.
>
> Sheet 1 is a summary that displays data from the other 4 sheets, the name of
> one of which is "Northeast" (though experimentation proves that it doesn't
> matter what the sheet is named)
>
> 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 (without the B: C: D: E: and F
>
> B: =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> C: =SUM(Northeast!C:C)
> D: =SUM(Northeast!D:D)
> E: =1-B4/ABS(C4)
> F: =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>
> The other rows have identical formula constructs with only the referenced
> sheet name changed and all work perfectly.
>
> As is F4 displays "#VALUE". I have verified that the formula is exactly as
> it should be and aside from the reference to that particular worksheet is
> identical to the formula in column F in the other rows.
>
> 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!
>
> To be very clear, in F6 the formula
>
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in 56.16666667
>
> In F7 the formula
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in #VALUE!
>
> Can anybody shed some light on the situation? Why does the result of a
> formula depend on which cell it is in?

--

Dave Peterson

[Max]

Just some guesses / thoughts ..

> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
The above will return #NUM! anywhere if it is *correctly* entered with
CTRL+SHIFT+ENTER (instead of just pressing ENTER). It's an array formula
which requires that special way of entering the formula. And think the error
#NUM! results because you can't use entire column references (B:B, C:C, etc)
in the formula

Try instead, something like:
=AVERAGE(IF(Northeast!B1:B10<>0, Northeast!B1:B10,""))
and array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Wowbagger" <none> wrote in message
news:[email protected]...
> I have a multi-sheet 2003 workbook.
>
> Sheet 1 is a summary that displays data from the other 4 sheets, the name
of
> one of which is "Northeast" (though experimentation proves that it doesn't
> matter what the sheet is named)
>
> 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 (without the B: C: D: E: and F
>
> B: =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> C: =SUM(Northeast!C:C)
> D: =SUM(Northeast!D:D)
> E: =1-B4/ABS(C4)
> F: =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>
> The other rows have identical formula constructs with only the referenced
> sheet name changed and all work perfectly.
>
> As is F4 displays "#VALUE". I have verified that the formula is exactly
as
> it should be and aside from the reference to that particular worksheet is
> identical to the formula in column F in the other rows.
>
> 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!
>
> To be very clear, in F6 the formula
>
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in 56.16666667
>
> In F7 the formula
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> results in #VALUE!
>
> Can anybody shed some light on the situation? Why does the result of a
> formula depend on which cell it is in?
>
>

[Wowbagger]

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> The formula in F4:
> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> is an array formula (you used ctrl-shift-enter instead of just entering it
> into
> the cell)

Don't array formulas always appear with {}s around them?

> But array formulas don't work on whole columns:
> =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
>
> Should work ok. (But I'd use a range that was big enough--but not too
> big.)

I tried with B1:B1000 as the range instead of B:B and I get the same
problem: and the same issue with the formula working in some cells but not
in others. Even cells that never had any formula in them at all.

Interestingly enough I've found another weird aspect to this problem.

I cut/pasted row 4 to row 6 and the formula works fine (as it always has).
Thinking that there may be some weird corruption or strange formatting
glitch I deleted row 4 from the database. Row 5 (which was now row 4)
stopped working and generated the same error.

Everything works fine as long as I leave row 4 empty. What could possibly
be causing this?

>
> Wowbagger wrote:
>>
>> I have a multi-sheet 2003 workbook.
>>
>> Sheet 1 is a summary that displays data from the other 4 sheets, the name
>> of
>> one of which is "Northeast" (though experimentation proves that it
>> doesn't
>> matter what the sheet is named)
>>
>> 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 (without the B: C: D: E: and F
>>
>> B: =SUMIF(Northeast!C:C,">0",Northeast!B:B)
>> C: =SUM(Northeast!C:C)
>> D: =SUM(Northeast!D:D)
>> E: =1-B4/ABS(C4)
>> F: =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>>
>> The other rows have identical formula constructs with only the referenced
>> sheet name changed and all work perfectly.
>>
>> As is F4 displays "#VALUE". I have verified that the formula is exactly
>> as
>> it should be and aside from the reference to that particular worksheet is
>> identical to the formula in column F in the other rows.
>>
>> 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!
>>
>> To be very clear, in F6 the formula
>>
>> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> results in 56.16666667
>>
>> In F7 the formula
>> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> results in #VALUE!
>>
>> Can anybody shed some light on the situation? Why does the result of a
>> formula depend on which cell it is in?
>
> --
>
> Dave Peterson

[Dave Peterson]

Array formulas will have the curly brackets around them if you enter them
correctly.

Do you have any merged cells in that range?

Did you adjust the range in each spot?

Do you have any errors in that range (B1:B1000)?

If you open a new workbook and try it there, does it work ok?

Wowbagger wrote:
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > The formula in F4:
> > =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> > is an array formula (you used ctrl-shift-enter instead of just entering it
> > into
> > the cell)
>
> Don't array formulas always appear with {}s around them?
>
> > But array formulas don't work on whole columns:
> > =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
> >
> > Should work ok. (But I'd use a range that was big enough--but not too
> > big.)
>
> I tried with B1:B1000 as the range instead of B:B and I get the same
> problem: and the same issue with the formula working in some cells but not
> in others. Even cells that never had any formula in them at all.
>
> Interestingly enough I've found another weird aspect to this problem.
>
> I cut/pasted row 4 to row 6 and the formula works fine (as it always has).
> Thinking that there may be some weird corruption or strange formatting
> glitch I deleted row 4 from the database. Row 5 (which was now row 4)
> stopped working and generated the same error.
>
> Everything works fine as long as I leave row 4 empty. What could possibly
> be causing this?
>
> >
> > Wowbagger wrote:
> >>
> >> I have a multi-sheet 2003 workbook.
> >>
> >> Sheet 1 is a summary that displays data from the other 4 sheets, the name
> >> of
> >> one of which is "Northeast" (though experimentation proves that it
> >> doesn't
> >> matter what the sheet is named)
> >>
> >> 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 (without the B: C: D: E: and F
> >>
> >> B: =SUMIF(Northeast!C:C,">0",Northeast!B:B)
> >> C: =SUM(Northeast!C:C)
> >> D: =SUM(Northeast!D:D)
> >> E: =1-B4/ABS(C4)
> >> F: =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >>
> >> The other rows have identical formula constructs with only the referenced
> >> sheet name changed and all work perfectly.
> >>
> >> As is F4 displays "#VALUE". I have verified that the formula is exactly
> >> as
> >> it should be and aside from the reference to that particular worksheet is
> >> identical to the formula in column F in the other rows.
> >>
> >> 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!
> >>
> >> To be very clear, in F6 the formula
> >>
> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >> results in 56.16666667
> >>
> >> In F7 the formula
> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
> >> results in #VALUE!
> >>
> >> Can anybody shed some light on the situation? Why does the result of a
> >> formula depend on which cell it is in?
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Wowbagger]

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...

> Do you have any merged cells in that range?

No.

> Did you adjust the range in each spot?

I'm not sure what you mean.

> Do you have any errors in that range (B1:B1000)?

No.

> If you open a new workbook and try it there, does it work ok?

No: I get the same error, always in row 4.


>
> Wowbagger wrote:
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > The formula in F4:
>> > =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> > is an array formula (you used ctrl-shift-enter instead of just entering
>> > it
>> > into
>> > the cell)
>>
>> Don't array formulas always appear with {}s around them?
>>
>> > But array formulas don't work on whole columns:
>> > =AVERAGE(IF(Northeast!B1:B65535<>0, Northeast!B1:B65535,""))
>> >
>> > Should work ok. (But I'd use a range that was big enough--but not too
>> > big.)
>>
>> I tried with B1:B1000 as the range instead of B:B and I get the same
>> problem: and the same issue with the formula working in some cells but
>> not
>> in others. Even cells that never had any formula in them at all.
>>
>> Interestingly enough I've found another weird aspect to this problem.
>>
>> I cut/pasted row 4 to row 6 and the formula works fine (as it always
>> has).
>> Thinking that there may be some weird corruption or strange formatting
>> glitch I deleted row 4 from the database. Row 5 (which was now row 4)
>> stopped working and generated the same error.
>>
>> Everything works fine as long as I leave row 4 empty. What could
>> possibly
>> be causing this?
>>
>> >
>> > Wowbagger wrote:
>> >>
>> >> I have a multi-sheet 2003 workbook.
>> >>
>> >> Sheet 1 is a summary that displays data from the other 4 sheets, the
>> >> name
>> >> of
>> >> one of which is "Northeast" (though experimentation proves that it
>> >> doesn't
>> >> matter what the sheet is named)
>> >>
>> >> 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 (without the B: C: D: E: and F
>> >>
>> >> B: =SUMIF(Northeast!C:C,">0",Northeast!B:B)
>> >> C: =SUM(Northeast!C:C)
>> >> D: =SUM(Northeast!D:D)
>> >> E: =1-B4/ABS(C4)
>> >> F: =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >>
>> >> The other rows have identical formula constructs with only the
>> >> referenced
>> >> sheet name changed and all work perfectly.
>> >>
>> >> As is F4 displays "#VALUE". I have verified that the formula is
>> >> exactly
>> >> as
>> >> it should be and aside from the reference to that particular worksheet
>> >> is
>> >> identical to the formula in column F in the other rows.
>> >>
>> >> 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!
>> >>
>> >> To be very clear, in F6 the formula
>> >>
>> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >> results in 56.16666667
>> >>
>> >> In F7 the formula
>> >> =AVERAGE(IF(Northeast!B:B<>0, Northeast!B:B,""))
>> >> results in #VALUE!
>> >>
>> >> Can anybody shed some light on the situation? Why does the result of
>> >> a
>> >> formula depend on which cell it is in?
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Max]

> > Did you adjust the range in each spot?
> I'm not sure what you mean.

Just a shot at re-tracing the thread relevant to the above exchange .. I'm
not sure if the phenomena you're facing, assuming the formula's now
correctly array-entered, was because the cell references were not fixed,
i.e. you copied (to other cells):

=AVERAGE(IF(Northeast!B1:B1000<>0, Northeast!B1:B1000,""))

instead of:

=AVERAGE(IF(Northeast!$B$1:$B$1000<>0, Northeast!$B$1:$B$1000,""))

Some strange things/results could happen <g> if the cell references are not
fixed where they should be.

Do hang around for Dave's comments, though ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Dave Peterson]

My question was more of a typo test:

=AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))

(just checking if B1:B1000 was used in both portions of the formula:


Max wrote:
>
> > > Did you adjust the range in each spot?
> > I'm not sure what you mean.
>
> Just a shot at re-tracing the thread relevant to the above exchange .. I'm
> not sure if the phenomena you're facing, assuming the formula's now
> correctly array-entered, was because the cell references were not fixed,
> i.e. you copied (to other cells):
>
> =AVERAGE(IF(Northeast!B1:B1000<>0, Northeast!B1:B1000,""))
>
> instead of:
>
> =AVERAGE(IF(Northeast!$B$1:$B$1000<>0, Northeast!$B$1:$B$1000,""))
>
> Some strange things/results could happen <g> if the cell references are not
> fixed where they should be.
>
> Do hang around for Dave's comments, though ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----

--

Dave Peterson

[Wowbagger]

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> My question was more of a typo test:
>
> =AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))
>
> (just checking if B1:B1000 was used in both portions of the formula:

Yes. As were B:B and B:B

[Dave Peterson]

I don't have any more guesses.

Wowbagger wrote:
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > My question was more of a typo test:
> >
> > =AVERAGE(IF(Northeast!B1:B1000<>0,Northeast!B1:B1000,""))
> >
> > (just checking if B1:B1000 was used in both portions of the formula:
>
> Yes. As were B:B and B:B

--

Dave Peterson

[Max]

Maybe you'd like to upload a small zip copy of your file via:
http://flypicture.com/ (see caveat below**)
and then post the *link* to it here

Might be easier for those interested in your problem
to see what the deuce is going on over there

(Note: Do NOT post any attachments !)

** I'm not promoting the site, but it is a free, easy-to-use,
no subscribe service where one can upload files for a 30 day stretch ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Wowbagger]

http://flypicture.com/p.cfm?id=53934

"Max" <[email protected]> wrote in message
news:[email protected]...
> Maybe you'd like to upload a small zip copy of your file via:
> http://flypicture.com/ (see caveat below**)
> and then post the *link* to it here
>
> Might be easier for those interested in your problem
> to see what the deuce is going on over there
>
> (Note: Do NOT post any attachments !)
>
> ** I'm not promoting the site, but it is a free, easy-to-use,
> no subscribe service where one can upload files for a 30 day stretch ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

[Max]

Here's the corrected sample file with
implemented formulas in "Summary-Revised":
http://flypicture.com/p.cfm?id=54060

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: weird-file_wowbagger_newusers.xls

------
Think everything looks fine now ! Since A2:A5 holds the 4 sheetnames, think
we can use INDIRECT to point to A2:A5 in the formulas for cols B, C and F.
This allows us to then simply copy down the formulas from the ones placed in
the starting row.

Put in B2:
=SUMIF(INDIRECT("'"&$A2&"'!C:C"),">0",INDIRECT("'"&$A2&"'!B:B"))

Put in C2:
=SUM(INDIRECT("'"&$A2&"'!C:C"))

Select B2:C2, copy down to C5

Put in F2, array-enter (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(INDIRECT("'"&A2&"'!B2:B100")<>0,INDIRECT("'"&A2&"'!B2:B100"),"")
)

Copy F2 down to F5

Note that the range used in F2 is corrected to "B2:B100", not "B1:B100"
since data starts from the 2nd row.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a new link to the sample file:
http://www.savefile.com/files/1042706
File: weird-file_wowbagger_newusers.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----