How can I add nested AVG in IFERROR

sshariati

My current formula is:

=IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023)), " ")

How can I add additional Months? I've tried:

=IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023))), " "), IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "March", {Calculations Range 3}, 2023))), " ")

and

=IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023))), AVG(COLLECT({Incident Duration}, {Month}, "March", {Calculations Range 3}, 2023))), " ")

but neither works

Goal: Obtain averages for each month and subsequent year on another sheet. If sheet where value is being obtained has blank value in Incident Duration column, then output should be a blank field using IFERROR.

brianschmidt

You were closer with your second formula in that iferror only needs to be in the formula once.

What are you hoping to accomplish in the center of the formula? Is the aim to show averages based on a month selection somewhere else in the sheet? If so, I would your nested "IF" formulas...which may look something like this:

=IFERROR(IF(Month@row="February", AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023)), IF(Month@row="March", AVG(COLLECT({Incident Duration}, {Month}, "March", {Calculations Range 3}, 2023)))), " ")

sshariati

Yes, the goal is to show averages based off of month and year. The month and year data is located in separate sheet, not on the same sheet where the formula is located. Incident Duration (the average of all this data I am looking for), Month, Year is located in one sheet and I want the formula to be in a separate sheet.

Basically each month per year there is a set of "incident durations" that I want to average out.

I tried the formula you provided but it didn't work 🤔

btw, thank you for your help!

brianschmidt

If I'm understanding correctly, you will need to have some condition to dictate what month to pull into that cell. For example, if you are wanting to show March averages, you'll either want just a formula in that cell that populates March averages (and likely separate cells, columns or summary data for each month) or some sort of selection that filters what month's averages to calculate. In my example above, the selection was a column where months would appear ("Month" column). Basically, the formula (though it only includes February and March selections) reads the value in the month column and knows what formula to run based on that selection (i.e. March is selected, so it runs the formula with cross-sheet references pulling from the other sheet. Here's that formula again for reference:

=IFERROR(IF(Month@row="February", AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023)), IF(Month@row="March", AVG(COLLECT({Incident Duration}, {Month}, "March", {Calculations Range 3}, 2023)))), " ")

Again, I think I might be missing something here. Are you able to share a screenshot of the sheet you're working in (with sensitive info covered)?

Otherwise, hope my explanation above makes sense:)

sshariati

Thank you for your feedback. Here are screen shots using dummy variables.

Screen shot #1 displays where I need the average to be outputted/displayed (green highlight). It is on a separate sheet and where I am seeking to include formula to generate calculation - within the green column. It should apply to all months and years.

The data to include in formula resides in sheet #2. The highlighted gray is where I used a formula to parse/divide the date into month name and year. Month and Year are "number/text" formats.

I'd like to get the average of all the Incident duration values per month and corresponding years and output to sheet #1. Using one formula that will apply to all months. Hope this makes sense, thanks again

Formula provided above, still showing #unparseable

Paul Newcome

Try this:

=AVG(COLLECT({Duration}, {Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Month of Incidents]@row), IFERROR(YEAR(@cell), 0) = YEAR([Month of Incidents]@row))))

sshariati

Thank you for your help, that didn't seem to work either

Paul Newcome

When you say it didn't seem to work, did you get an error message or an unexpected number?