Filtering based on multiple columns

Question

Level 1

4 points

Filtering based on multiple columns

In numbers, I want to calculate the standard deviation from Column F (rows 2-201), but ONLY when the matching rows from Column C AND Column E are 1. I can't figure out how to filter for both C and E. I can successfully filter for one of the columns and get the STDEV (the formula looks like this: STDEV($F$2:$F$201;IF(E;1;)) but any attempt to use multiple IF statements seem to fail.

For context, C and E essentially contains binary values (one is for gender, and one is for treatment group/control group), so I want to be able to calculate the standard deviation for both the treatment and control group, for both males and females.

Posted on Mar 4, 2025 3:14 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,550 points

Mar 4, 2025 3:29 AM in response to AltSamMan

I'm not sure that's a valid Numbers formula!

Could you post a screenshot?

(Shift-command-4, drag to select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon beneath the compose window to attach a screenshot from the Desktop.)

Note that things are much easier in Numbers if you reference the entire column F (rather than the Excel-like $F$2:$F$201).

Note that you can use the SUBTOTAL function to perform STDEV_H and other operations that skip rows that are hidden (manually or by applying a filter, for say, 1, on multiple columns).

SUBTOTAL - Apple Support

Filter data in Numbers on Mac - Apple Support

That may be a more natural way to accomplish what you are trying to do.

SG

Reply

Answer 2

Badunit

Level 7

30,366 points

Mar 4, 2025 5:19 AM in response to AltSamMan

Excel has "array" formulas that can let you "filter" data and create a subset array of the data within the formula. Numbers does not have that. If you want a subset of the data, you need to do it in the table, usually with an extra column. Your formula does not look like a correct array formula for Excel. It looks like like it would insert a bunch of 1's and FALSE's into your data set in Excel. In Numbers it will look at the value in column E that is in the same row as the formula and insert a single 1 or FALSE.

The typical way to create a subset of data is to use a new column.

Column G formula = IF(E,F,"")

Then use STDEV(G).

Another way you can do it for a few functions, including STDEV, is with filtering and the SUBTOTAL function.

=SUBTOTAL(107,F) will use only the visible rows in column F. If you put a filter on the table to hide the rows you do not want included, those hidden rows will not be included.

Reply

Answer 3

AltSamMan Author

Level 1

4 points

Mar 4, 2025 5:12 AM in response to SGIII

So, I managed to get a little further on my mad quest, so here's a screenshot from where I'm stuck now.

I'm trying to create the STDEV for column F, filtering out only the rows where column B is 2 (2 is an age group) and column E is 1 (1 means they're in the treatment group).

The formula actually returns a result, but the result doesn't change when I change the age group (so if I replace the 2 after the reference to column B with a 3, it gives the same number). So clearly I'm doing SOMETHING wrong, but I can't figure out what.

Reply