Is there a way to create collection from a selection of cells in a column?

Question

Is there a way to create collection from a selection of cells in a column?

The CORREL function takes a collection as a parameter. Is it possible to create such a collection by filtering a column using a condition on another column?

For example, if I have a table with one column containing the Answer to a survey question, and another containing the Gender of the respondent, is the some way to run CORREL to find the correlation between Male and Female Answers.

if I wanted the average of a numeric answer from each gender, I would do that with SUMIF and COUNTIF. How could I do something similar to find the correlation coefficient for the Answers?

MacBook Air 13″, macOS 10.13

Posted on May 6, 2024 2:10 PM

Reply

Answer 1

Badunit

Level 7

26,242 points

May 6, 2024 7:42 PM in response to stuartfromtwickenham

In Excel you can with "array" formulas, not in Numbers.

A lot of functions will ignore rows with text or empty cells. I am not entirely sure what you are trying to do or how to do it, but if you separate gender into separate columns you might be able to create these "collections".

As an example,

Column A is a column of answers with numeric values (like 1-4)

Column B is 1 if the respondent was male or "" (null string) if not male.

Column C is 1 if the respondent was female or "" (null string) if not female.

SUMPRODUCT(A,B) will give the sum of the answers for male respondents.

Yes, you can use SUMIF to do this same thing. This is just an example of how you might be able to make the collections you are asking about.

You can keep your original column for gender and use functions to populate separate columns with 1's and null strings. You can hide those extra columns after everything is set up and working.

I don't know if any of this will help or not.

You may need more columns than Numbers allows if you want to include all the "genders". It is an ever-growing list. If you transpose the table so each row is a gender, you can have up to 1 million genders.

Reply

Answer 2

May 7, 2024 7:27 AM in response to Badunit

Thank you, so much, for the feedback.

The insight that CORREL ignores NULL cells is a good tip. There are only two genders. So I can simply create two additional tables, that are identical to the original but filtered on gender (so that rows not of the appropriate gender are all null) then do the correlation on each of those tables.

It would be nice if there was a general function, something like SELECTIF, what would return a collection containing a set cells based on applying a condition to another column. I am sure there are other uses for that.

But, anyway, thanks for your help. It solves my problem.

Have a great day

Reply