How to filter and average numbers by leader

Nina R

Hello,

I have been trying to write a formula to pull the averages of numbers in each column by Leader into a separate sheet but the only formula I could find that can calculate this is working, but pulling the data as zero. Is the issue because this is data generated from a form?

The equation I am using is =AVERAGEIF({30-Day Check-In: Standardization Process Range 6}, =Leader@row) - it's pulling data from another sheet, BUT for every leader, it is coming up as zero. Please Help!

JamesB

@Nina R Your formula is not complete.

Averageif usage is the range to look at, the criteria in the range, and the range to average.

=AVERAGEIF({30-Day Check-In: Standardization Process Range 6}, Leader@row, [I Understand the key components of Standardization]:[I Understand the key components of Standardization])

Nina R

Thanks James! I tried that too, but when I enter that equation, it comes back as an incorrect argument set. Thoughts?

Bassam Khalil

Hi @Nina R,

Here are some things to check and suggestions for adjustments:

1. Correct Syntax for AVERAGEIF: Ensure the syntax of your AVERAGEIF function is correct. The standard syntax is AVERAGEIF(range, criteria, [average_range]). It looks like you might have an extra equals sign (=) before Leader@row which isn't necessary. The correct format doesn't require = before the criteria when referencing another cell or using a named range.
2. Criteria Matching: Verify that the criteria (Leader@row) matches exactly with the entries in your specified range. This includes checking for any subtle differences like extra spaces, capitalization, or special characters.
3. Range and Average Range: In your formula, you only specified the condition range. If your numbers to be averaged are in the same range as your criteria, this setup is fine. However, if the numbers to be averaged are in a different column, you need to specify this third parameter in your function.
4. Data Types: Ensure that the data in the column you're trying to average is indeed numerical. Sometimes, data imported from forms might be interpreted as text, especially if there are any non-numeric characters present. If the data is being interpreted as text, the AVERAGEIF function would not be able to calculate an average, resulting in a zero output.
5. Permission Issues: If the range you are trying to access is on another sheet, make sure you have the correct permissions to access that data.

Given the points above, here’s how you might adjust your formula:

• If the numbers to average are in the same column as the leaders:

=AVERAGEIF({30-Day Check-In: Standardization Process Range 6}, Leader@row)

If the numbers to average are in a different column, but you still filter by the "Leader" in the mentioned range, you'll need to add that average range as the third parameter. Assuming the numbers are in a range named {Number Range}, the formula would look something like:

=AVERAGEIF({30-Day Check-In: Standardization Process Range 6}, Leader@row, {Number Range})

Ensure that your range names are correctly specified and that you're using the right syntax for referencing cells and ranges. Also, double-check the data type of the entries in the column you wish to average. If these steps don't resolve the issue, it might be helpful to manually check a few entries to ensure they meet the criteria and are indeed numerical.