6 Replies Latest reply: Oct 26, 2013 11:27 AM by Thenamesdave
Thenamesdave Level 1 Level 1 (0 points)

I use a ton of SUMIFS and AVERAGEIFS formulae on a monster of a sports stats spreadsheet and it seems that since upgrading to Numbers 3.0, some references have been lost but not others.  In other words, some of my SUMIFS and AVERAGEIFS work as expected and others not.  For the ones that don't, however, I can SEE the data and there's nothing different about it in terms of format than what's in the formulae that works.  I don't understand why Numbers can't see the columns I'm seeing it.  What is going on here? Could it be that the columns are very far "east," like in the FJ to IJ range? Could this be user error on my part?


MacBook Pro 2.8 GHz
  • 1. Re: Invalid reference on data I can see! What gives?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Dave,

     

    How about posting a Screen Shot and Pasting the formula that doesn't work here in the Discussion editor?

     

    Jerry

  • 2. Re: Invalid reference on data I can see! What gives?
    Thenamesdave Level 1 Level 1 (0 points)

    Busted formula example 1.jpg

    Sorry if you're not familiar with ice hockey stats. I want to know the total number and per-game average number of shots on goal taken at 5-on-5 manpower by my team (the New York Rangers) and by the opposition in each individual period of play during games on the road.  This works without any issue on the Rangers data (first image below) but fails on the opposition data (second image).  The data I am trying to sum and average is the last image.

    Busted formula example 2.png (This one works.)

    Busted formula example 3.png (This one fails. Why?!)

    Busted formula example 4.png (This is the column that numbers is seemingly unable to find. It's on another sheet.)

  • 3. Re: Invalid reference on data I can see! What gives?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    My suggestion is to try changing the Numbers > Preference > General for Cell References, Use Header Names as References. Then reenter the reference and see what happens. I'll bet it works.

     

    My speculation is that the compiler choked on the long header text.

     

    Jerry

  • 4. Re: Invalid reference on data I can see! What gives?
    Thenamesdave Level 1 Level 1 (0 points)

    Sorry, but that did not correct the issue.


    Do you think there is some issue caused by the different colors in the formula arguments?  When the formula works, the first argument (the range of data being summed/averaged) is blue, but when the formula fails, it is purple...

  • 5. Re: Invalid reference on data I can see! What gives?
    SGIII Level 5 Level 5 (4,130 points)

    I've seen this quite a bit on imported stuff. The column anchors sometimes seem to be dropped so the formulas are referring to the wrong columns. In Numbers 3.0 to a #REF! or a wrong column reference, click the oval, then click the column letter above the relevant column in the table it's supposed to refer to:

     

    Screen Shot 2013-10-26 at 1.06.22 PM.png

     

     

    The column name (or letter if you have 'Use header names as labels' turned off in preferences) should change in the oval.  If it looks right, click the triangle and tick 'Preserve Column'.

     

    preserve-column.png

     

    Repeat as needed with the other ovals (references) in the formula

     

    SG

  • 6. Re: Invalid reference on data I can see! What gives?
    Thenamesdave Level 1 Level 1 (0 points)

    Thank you!  That worked.  Not sure why it's happening, but the fix is in.  Much appreciated.  Have a great day, everyone.