Another low-stakes spreadsheet question

February 11, 2025 6:27 PM

I have a spreadsheet filled with data collected over the course of the year. I’ve also created a secondary sheet (pivot table?) showing week-by-week summaries aggregated from the main sheet. In the pivot table I want to prevent negative values from being included in one of the AVERAGEIFS formulas that I’m currently using. How do I do this? I’m working in Apple Numbers, but formulas seem to work the same as in Excel for the most part.

This spreadsheet is mostly for fun and somewhat for learning purposes. The stakes are low. Without getting too mired in details, the data in the main sheet is from cycling workouts — miles ridden, elevation gained, all that good stuff. One of the columns is for heart rate recovery (HRR), which is simply the number of bpm the heart has slowed after 2 minutes of post workout cool-down. Negative values in this column are erroneous for my purposes (it just means I did something strenuous during cool-down and fouled the HRR data).

So how do I exclude those negative values from calculations in the pivot table, where I’m looking at my average HRR per week?

Below is a simplified version of the relevant formula that I’m currently using in the pivot table. (The main spreadsheet is named “2024”, with HRR data from 200 workouts in column A and the weeks of the year in column B. And Z1 is from the column of the pivot table that shows what week I’m aggregating for):

AVERAGEIFS(2024::$A$1:$A$200,2024::$B$1:$B$200,Z1)

This formula works as intended in the pivot table—it shows average HRR for a given week. I just can’t figure out where I’m supposed to insert ">0" to exclude negative values from column A of the main sheet.

posted by theory to Computers & Internet (4 answers total)

does the filter() function work with Apple Numbers?

AVERAGE(FILTER($A$1:$A$200,($A$1:$A$200>0)*($B$1:$B$200=Z1),""))

If not, how about N()? Less elegant, but this worked for me in Excel:
SUMPRODUCT($A$1:$A$200,N($A$1:$A$200>0),N($B$1:$B$200=Z1))/SUMPRODUCT(N($A$1:$A$200>0),N($B$1:$B$200=Z1))
posted by mullacc at 6:55 PM on February 11

How about a helper column that only includes positive values?
posted by jmfitch at 7:17 PM on February 11

Best answer: I think the answer to the question your asking is probably either the filter option or the helper column, but I want to suggest that maybe it would be better to clean up your data rather than figure out how to deal with dirty data. Think about whether you want those "did something strenuous in cooldown" situations to be gone or just set to 0 (so you didn't actually cool down). Then instead of calculating "WorkoutHR-CooldownHR" do

=If ((workouthr-cooldownHR>-1), (workouthr-cooldownHR), 0) (For entering a 0)
or
=If ((workouthr-cooldownHR>-1), (workouthr-cooldownHR), "") (For entering a blank).

Another way to enter the 0 would be =MAX(0, (workouthr-cooldownhr)

Then when you do the averages these will enter as 0s or not at all, whatever your preference is. Sure you could do it one of the other ways, but your input data would still be wrong and who wants wrong data. Fix the problem at its origin.
posted by If only I had a penguin... at 9:12 PM on February 11 [1 favorite]

I agree with our local penguin fancier. When I do this kind of thing I start by creating three sheets rather than two; sheet 1 has my input data and sheet 2 has the cleaned-up version of that upon which the reports in sheets 3 and onward will be based.

I always make the layout of sheet 2 identical to that of sheet 1. This makes it easy to use conditional formatting on sheet 1 to put a pale yellow background behind any cell where sheets 1 and 2 differ, which is helpful for catching data that's only dirty because I entered it wrong.
posted by flabdablet at 6:39 AM on February 12