Numbers syntax error

Question

Level 1

39 points

Numbers syntax error

Hi, I have a table with meter readings over 365 days for which I made a separate table to show daily average use. AVERAGEIF works well except one aspect is a bit clunky. Method used as follows:

DAYNAME in a separate column derives day from timestamp column.

AVERAGEIF works if I manually type text within "".

But if (instead of manually typing day names into the formula) I try to reference text (from a column which already has the day names) as follows, it gives error:

I've tried using "" in all sorts of combinations but I'm missing something obvious here.

Any help appreciated. Thank you.

Posted on Oct 17, 2021 2:33 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Oct 17, 2021 7:57 AM in response to SaltSeaShark

AVERAGEIF is a solid solution. But keep in mind that with just a few clicks and drags you can create a Pivot Table that does the same thing. Much faster. No formulas!

Start by clicking the original table and in the menu Organize > Create Pivot Table > On Current Sheet.

Play around a little. You'll be surprised at what you can do with so little effort.

SG

Reply

Answer 2

HD

Level 5

4,945 points

Oct 17, 2021 10:32 AM in response to SaltSeaShark

The best site I know for Regular Expressions tutorials, guides and more is here. In my limited experience it helps to learn with a text editor like BBEdit or CotEditor (both available in free versions from the App Store). There's quite a steep learning curve which doesn't really lend itself to YouTube videos 🤓

Reply

Answer 3

Yellowbox

Level 7

21,939 points

Oct 17, 2021 3:33 AM in response to SaltSeaShark

Hi SaltSeaShark,

No need for the = sign if you use a cell reference as the condition. The = is implied.

Try this:

AVERAGEIF(Day Reference,$A2,Usage)

Regards,

Ian.

Reply

Answer 4

SaltSeaShark Author

Level 1

39 points

Oct 17, 2021 5:17 AM in response to Yellowbox

Hi Ian,

Massive thanks, worked perfectly.

As an aside, the Numbers help documents are generally quite good, but I think they could be much better. I've just learned the REGEX function (which is massively useful) but relied mainly on YouTube videos (e.g. excellent macmostvideo) to learn boolean terms. AFAIK there's no comprehensive list anywhere on Apple site.

Reply