Formula help for averaging data in 1 column based on criteria from 2 other columns

JBishop

I need help with a formula that will average the total in 1 column based on 3 criteria of another column and 1 criteria of another column.

To phrase better, I have a column of dollar amounts that I need to average based upon the sum of all values in that column. BUT, the values to sum are based upon 3 status values of another column and yet an additional column dependent upon field that have no blank values.

• Column 1 just has dollar values the will be used to provide an overall average dollar amount
• Column 2 has status values such as complete, active, in review, etc.
• Column 3 has dollar values but must not be blank

L_123

Couple things. First of all you should name your ranges whenever you make them. It will make editing, improving, and troubleshooting your references much easier in the future.

Second, if you are ever troubleshooting a "collect" formula, I recommend changing whatever consolidator (in your case avg) to join. That way you can see what is being collected.

That said, you are receiving the error you are because the collect isn't returning a value. If you put join instead of avg, you will return a blank. your syntax is correct, but according to what you wrote in the bottom, your column references aren't in the correct locations.

=AVG(COLLECT({Project Tracker Range 1}, {Project Tracker Range 3}, OR(@cell = "Status 1", @cell = "Status 2", @cell = "Status 3"), {Project Tracker Range 2}, @cell <>"")

Paul Newcome

Try something like this...

=AVG(COLLECT({Column 1}, {Column 2}, OR(@cell = "Status 1", @cell = "Status 2", @cell = "Status 3"), {Column 3}, @cell <> ""))

JBishop

Hi Paul,

Thank you for responding so quickly. Are the 3 {Column#} references calling for the sheet range or are you specifically referring to columns? I'm a little confused because I don't see any references to the 3 sheet column ranges that I need to pull from.

Thank you.

JBishop

Here's what I have so far from what I believe you have written...but clearly isn't working. I'm getting a "#DIVIDE BY ZERO" error

=AVG(COLLECT({Project Tracker Range 3}, {Project Tracker Range 1}, OR(@cell = "Status 1", @cell = "Status 2", @cell = "Status 3"), {Project Tracker Range 2}, @cell <>"")

Project Tracker Range 3 = the column with the 3 status values

Project Tracker Range 1 = the column with the dollar values that need to be averaged

Project Tracker Range 2 = the column with the fields that should not be blank

Hope this helps. Thanks again!

L_123

Couple things. First of all you should name your ranges whenever you make them. It will make editing, improving, and troubleshooting your references much easier in the future.

Second, if you are ever troubleshooting a "collect" formula, I recommend changing whatever consolidator (in your case avg) to join. That way you can see what is being collected.

That said, you are receiving the error you are because the collect isn't returning a value. If you put join instead of avg, you will return a blank. your syntax is correct, but according to what you wrote in the bottom, your column references aren't in the correct locations.

=AVG(COLLECT({Project Tracker Range 1}, {Project Tracker Range 3}, OR(@cell = "Status 1", @cell = "Status 2", @cell = "Status 3"), {Project Tracker Range 2}, @cell <>"")

JBishop

OMG!! You are my hero! I've been working on this for days.

I can't thank you enough for all your help!

Paul Newcome

Apologies for the late reply. I was referring to the columns as you had them listed in your original post, but I see that @L_123 was able to clear that up for you.

JBishop

@Paul Newcome, thank you for following up. You and @L_123 both did a phenomenal job helping me. I can't thank you both enough!!