Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Invalid reference on data I can see! What gives?

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

Posted on Oct 26, 2013 9:00 AM

Reply
6 replies

Oct 26, 2013 9:27 AM in response to Jerrold Green1

User uploaded file

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.

User uploaded file (This one works.)

User uploaded file (This one fails. Why?!)

User uploaded file (This is the column that numbers is seemingly unable to find. It's on another sheet.)

Oct 26, 2013 10:16 AM in response to Thenamesdave

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:


User uploaded file



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


User uploaded file


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


SG

Invalid reference on data I can see! What gives?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.