More efficient formula request

Hi All!

I am working on a spreadsheet with multiple columns of information. In one area I need to get an average of percentages, but there are some wrinkles:
1. The cells are not adjacent to each other
2. Some cells have a 0 value
3. Some cells have a -1 value

I want to get the average of the 6 non-adjacent cells excluding 0 & -1 values

I came up with the following formula, but there has got to be a better way.

Formula:

Please Login or Register  to view this content.

There are between 8 and 10 columns between each of the 6 values I am trying to average, below is a simple picture. I can't seem to use AVERAGEIF since that needs a continuous range, unless I am missing some?

Please let me know if there is a easier, better way to do this and thank you very much for any help you can provide!
Doug

Screenshot 2021-04-30 152529.png

Hi Sam,

the AVERAGEIF functions works in the sample I did, but in my actual workbook there are between 8 & 10 columns with information in them (other percentages) between each of the cells I am trying to average and AVERAGEIF won't work here (that I have been able to figure out/test) since it is not a range but several separate cells.

Hi AliGW,

You are correct, and I am attaching an example workbook for review.

There are 6 groupings of data (not tables) that show quarterly percentages and at the end a table that averages the percentages per quarter. For each summary quarter (end table) there are 6 inputs (i.e. Q1 Group 1, Q1 Group 2, etc.) which are separated by roughly 9 columns with a mix of text and other percentages (hence not being able to use AVERAGEIF since it works on a range...that I have been able to figure out).

I have had to come up with a couple of different formulas for averaging with ranges (the formulas can be found in the Annu. columns, the Combined Table, and the Weighted Average table).

Everything works, I am just looking for input on how to be more efficient if possible.

Note: The tab in this workbook looks up data from other sheets, I have just copy/pasted values to hopefully stop any connection issues and allow me to clean from private info.

Thank you all for any help or guidance you can provide.
Doug

Hi Sam,

I apologize for any confusion I caused. There isn't an issue or problem with what has been done; my inquiry has been if there is a better, more efficient way to accomplish what I am doing. I have the averageif within the groups of data (Spine, Brain, etc.) where I had to come up with the formula in my first post is on the far right combined and summary data since this is taking per question the first 1 value of each group and averaging it (i.e. Spine question 5 quarter 1 + Brain question 5 quarter 1, etc) and since there are columns between these values (roughly 9) with text and other quarterly values I cannot use a range that I know of. If you look at the formulas in the Last group of data (Performance Improvement - COMBINED) you will see the formula I cobbled together, The weighted averages data group also has a formula along similar lines.

Again, I apologize for any confusion and I really appreciate your time looking at this.
Doug

Hi Bebo02999,

That is amazing!!! This works and is much simpler that what I came up with. If I understand correctly this is working by defining a range for the values, a range for the criteria for the values, and then the specific criteria? I had no idea AVERAGEIFS worked this way; I just focused on a range for the values and overthought myself into something more complex.

Thank you!!!!
Doug