non error error

how come this = 0 and not #REF or something?

=SUMIFS(AccQTYMT,AccProduct,F$56,AccTradeType,F$57,AccStrategy,#REF!,AccMaturity,$D104)

=SUM(G8,#REF!) = #REF! as expected

=SUMIF(E7:E12,#REF!,F7:F12) = 0 too.

All in 2007 (and 2010), I don’t have a 2003 to check just now, is it the same? (just the SUMIF obviously, you dinosaurs don’t have the luxury of SUMIFS ;-))

OpenOffice returns the #REF! I would expect.

Would you expect a formula to return an error if one of its required arguments is an error?

In fairness if there are #REF!s in the data it matches them and returns the total, so in a literal sense it ‘works’, but I’m not sure its what I would expect. What about you?

cheers

Simon

Advertisements

12 Responses to “non error error”

  1. Andy Wall Says:

    Gives 0 in 2003 as well

  2. geoffness Says:

    Depends on what you understand errors to represent. If you see them working in the same way as nulls in SQL, i.e. unknown where x=unknown always evaluates to unknown, then the 0 is indeed unexpected. However if you see them as values, then they can be compared with other values and the 0 makes perfect sense.

  3. Simon Says:

    I’d expect them to propogate like they do in most other cases (and on OO calc), so they can be traced and fixed.

  4. Patrick O'Beirne Says:

    COUNTIF, DCOUNT, DSUM all treat the #REF! as a value to be matched.
    MATCH & LOOKUP propagate the error.
    That’s Excel’s way, I guess :-)
    Another one for the cabinet of curiosities.

  5. Biggus Dickus Says:

    Yes this is an inconsistency IMHO. It does mean that it would be possible to have a missing reference value in your total and that is not a good thing.

    It defininitely will make it hard to completely trust these formulae as the error will not be visible unless you select the cell and look at the refernce itself.

    Unfortunate but it’s the way it is and we have to watch out for it I guess.

    OTOH MS could fix this in a Service pack because there would be no argument for leaving it this way to protect legacy spreadsheets (??)

    Better send it on to the Excel Team, but I’m not sure to who as our friend Dave G isn’t there anymore :-(

    Dick

  6. Harlan Grove Says:

    This works the same in all versions which have SUMIF. It only works when error values are criteria, and that may be because criteria arguments are always handled as text strings. That is, Excel always and automtically converts the #REF! in the criteria arguments into “#REF!” (or maybe “=#REF!”).

    FAR WORSE, criteria beginning with don’t always produce the complementary result to criteria otherwise the same but beginning with =. For example, enter 1 in A1, =”1″ in A2, 5 in B1 and 66 in B2. Then compare the formulas

    =SUMIF(A1:A2,1,B1:B2)
    =SUMIF(A1:A2,”1″,B1:B2)
    =SUMIF(A1:A2,”1″,B1:B2)
    =SUMIF(A1:A2,”1*”,B1:B2)

    How many people would you think would be caught short by

    =SUMIF(A,”x”,B)

    not returning the same value as

    =SUM(B)-SUMIF(A,”x”,B)

    ?

    The problem is that Excel treats = criteria as matching both numbers and text when the criteria is numeric, but reverts to standard Excel semantics and distinguishes between text and numbers in criteria.

    This is old news. Poor design, but at this point I don’t see this changing.

  7. Harlan Grove Says:

    That’s ‘criteria beginning with <>’.

  8. Harlan Grove Says:

    And the 3rd through 5th formulas should be

    =SUMIF(A1:A2,”<>1″,B1:B2)
    =SUMIF(A1:A2,”<>1*”,B1:B2)

    =SUMIF(A,”<>x”,B)

  9. sam Says:

    There are other such inconsistencies

    A classic example is a the way in which a cell containing a single quote is treated by different features and formulas

    a) Autofilters , Find, CountBlank, Sumproduct/CountIF(s) treats the Cell as Blank
    b) Advanced Filters ,Sort, CountA,ISBLANK GoTo Special Blanks doesn’t

  10. Harlan Grove Says:

    @sam – differences between SUMPRODUCT and COUNTIF need to be expressed precisely. SUMPRODUCT(–(x=””)) and COUNTIF(x,””) will return the same result, but SUMPRODUCT(–ISBLANK(x)) can return a different result. A single quote should be treated as identical to “”.

  11. sam Says:

    @Harlan,
    You are right of course….I should have made that more clear.
    I am amazed at how things like this have continued uncorrected across versions….

  12. Harlan Grove Says:

    Backwards compatibility. Workarounds needed in one version should continue to work in subsequent versions.

    But the single quote is the syntactic means of entering a zero-length string constant. While it’s possible to generate a zero-length string constant by entering the formula =””, copying it, then pasting it in-place as a value, such a cell will APPEAR identical to a blank cell in the formula bar, but it won’t be a blank cell. To me that’s far more questionable than treating a single quote as equivalent to “”.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: