Average If using a list rather than a range

Jared Davis

I need to take an average if the data is greater than zero. I got it covered when the data is neatly organized in a range: =AVERAGEIF([Chart Data]79:[Chart Data]82, >0, [Chart Data]79:[Chart Data]82).

My issue comes in when I try the same formula for cells dispersed throughout the sheet. I need to add the average if not zero to this formula:

=AVG([Chart Data]78, [Chart Data]83, [Chart Data]87, [Chart Data]92)

I tried a semicolon thinking that would be somewhere between a colon and a comma (made sense to me...) but that made my computer crash...

Paul Newcome

I would suggest a checkbox column where the rows that you need to average are checked. Then you can use this column as the factor in your AVERAGEIF.

Paul Newcome

Haha. Thanks.

For the AVG/COLLECT, you are missing the first range in the COLLECT function. The easiest way to build this out would be to first build out the COLLECT.

COLLECT({Range to collect}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria)

COLLECT([Chart Data]78:[Chart Data]94, [AVG without zero]:[AVG without zero], @cell = 1, [Chart Data]78:[Chart Data]94, @cell > 0)

Now that you have collected what data you want, you can tell it what to do with that collected data which in this case is average it.

=AVG(COLLECT([Chart Data]78:[Chart Data]94, [AVG without zero]:[AVG without zero], @cell = 1, [Chart Data]78:[Chart Data]94, @cell > 0))

Out of curiosity... It looks like you have some regularly spaced formatting on your rows. Are you using hierarchy and creating parent and child rows? If so, there may be a way to leverage the hierarchy based functions which could possibly get rid of the need for the checkbox column.

Andrée Starå

Hi @Jason Davis

I hope you're well and safe!

@Paul Newcome Yes, I do. I'm in the middle of implementation of a Control Center project now, and two more next.

You can have up to three levels of hierarchy in the Summary, and you can select where new projects should be added in the Blueprint.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest a checkbox column where the rows that you need to average are checked. Then you can use this column as the factor in your AVERAGEIF.

Jared Davis

@Paul Newcome That answer is not as elegant and sophisticated as your beard, but I'll take it.

Jared Davis

I am working through this now - What is the formula exactly? I need to now Averageif on 2 criteria, check box =1 and value is greater than 0.

Tried AVG COLLECT, but it doesn't know what range to average.

Average IF returns the value but does not ignore the zeros:

Paul Newcome

Haha. Thanks.

For the AVG/COLLECT, you are missing the first range in the COLLECT function. The easiest way to build this out would be to first build out the COLLECT.

COLLECT({Range to collect}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria)

COLLECT([Chart Data]78:[Chart Data]94, [AVG without zero]:[AVG without zero], @cell = 1, [Chart Data]78:[Chart Data]94, @cell > 0)

Now that you have collected what data you want, you can tell it what to do with that collected data which in this case is average it.

=AVG(COLLECT([Chart Data]78:[Chart Data]94, [AVG without zero]:[AVG without zero], @cell = 1, [Chart Data]78:[Chart Data]94, @cell > 0))

Out of curiosity... It looks like you have some regularly spaced formatting on your rows. Are you using hierarchy and creating parent and child rows? If so, there may be a way to leverage the hierarchy based functions which could possibly get rid of the need for the checkbox column.

Jared Davis

That formula is baller. Thank you!

I will answer your curiosity question with a question. The sheet I am writing formulas in is my metadata sheet that is populated out of control center via an intake sheet. Control Center looks for the rows to populate in the intake sheet based on all children of the "Summary" line. If I add more hierarchy into this sheet, will Control Center still update the rows from the intake sheet?

Paul Newcome

Honestly I have zero experience with Control Center. I wish I could answer that. @Andrée Starå Do you have experience with Control Center to be able to answer that?

Andrée Starå

Hi @Jason Davis

I hope you're well and safe!

@Paul Newcome Yes, I do. I'm in the middle of implementation of a Control Center project now, and two more next.

You can have up to three levels of hierarchy in the Summary, and you can select where new projects should be added in the Blueprint.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jared Davis

Thanks @Andrée Starå and @Paul Newcome. That solves it. So to get the most elegant formula within my metadata sheet I can add more heirarchy and average children, all while maintaining the functionality of control center populating the data from the intake.

I must say I am a little giddy right now, I have been using answers that both of you provide on this forum for 6 years now, so thanks for the years of help. You guys are legends.

Paul Newcome

Happy to help. 👍️

I'm glad you can use hierarchy and leverage the CHILDREN function instead of having to specify exact ranges. It definitely makes life easier.

@Andrée Starå may have been providing answers for 6 years, but I have only been around here for 3. Haha. If one of my answers is from before that then maybe I am some kind of legend answering questions about a platform I never even knew existed. 🤣