AverageIf Function
I'm sure there is a very easy solution to this one, but need some help...
I want to capture the average of Range 29 IF Range 36 is marked as complete. I also enveloped this in the "Collect" function and the "If Error" function, but still no luck.
=AVERAGEIF({Range 29}, {Range 36}, "Complete")
I am receiving the "#INCORRECT ARGUMENT SET" error.
Answers
-
=AVERAGEIF({Range 36}, "Complete", {Range 29})
See if this does what you are looking for.
-
Hi @aecross
AVERAGEIF( range, criterion, AverageRange)
range is the range that holds the criterion (Range 36 in your example)
criterion is what you are looking for in the first range ("Complete" in your example, in Range 36)
AverageRange is the range to calculate average (Range 29 in your example)
AverageIf({Range 36}, ="Complete", {Range 29})
Hope this helps!
https://help.smartsheet.com/function/averageif?frame=0&nav=1
-
switch {Range 36} and {Range 29}
-
Thanks for the help, all!
I've flipped the ranges, but the outcome is "0" when i know it is definitely not. Any ideas how to solve this?
=AVERAGEIF({Range 36} = "Complete", {Range 29})
-
I'm guessing that Complete is a multi-select drop down column? If so, you're going to need to use CONTAINS to locate Complete in the multiple things selected in a drop down column.
-
Your formula isn't correct either. You're missing a comma and you don't need the equals sign.
=AVERAGEIF({Range 36}, "Complete", {Range 29})
If this doesn't work, we're back to the multi-select drop down possibility and needing to use CONTAINS to solve it.
-
How is the Range 36 column formatted?
-
Probably as a multi-select dropdown column :)
-
@Mike TV & @Carson Penticuff - it is a single-select drop down column.
How do i integrate CONTAINS into the formula?
-
If it's single select then don't use CONTAINS. That won't help fix it.
Include a screenshot of what you're doing so we can help fix.
-
@Mike TV - i am looking to get the average of the total only for projects that are listed as COMPLETE in the current project phase (Range 36).
The formula is referencing these items in a different sheet. Current project phase is a single-select dropdown, Total is a text a text column.
-
The formula I provided earlier seems like it should work in this scenario. Could you try this:
=COUNTIF({Range 36} "Complete")
This should spit out the number of Complete matches. If you verify whether this number is correct, that will tell us whether the issue is with matching this column, or calculating the other column.
-
Don't forget your comma
-
Using =COUNTIF({Range 7}, "Complete") I got 25 which is accurate.
-
The last 10 times I referenced the "Current project phase" column it came up as Range 36 ... for my COUNTIF it came up as Range 7. Not sure the issue but it's working now!!!
Thanks for all of the help, I appreciate!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!