Need help with a complex formula
Attempting to write a formula in a sheet summary field. Is it possible to write a formula that looks for particular rows based on the text value in one column, and then get the AVG of the numbers in a different column on those selected rows?
(ie. If the value in column Task is "Work Order Received", find the average of the Duration column on those rows across all projects on the sheet)
I've tried a bunch of different variations and keep getting different errors. Anyone ever written a successful formula like this?
@Paul Newcome , I will never not tag you in formula questions LOL
Answers
-
Hi Kelly. Are these the results you're looking for?
Example sheet:
Sheet Summary formula:
=AVERAGEIF(Task:Task, ="Work Order Received", Duration:Duration)
-
Also, if you have something like "12 h" in the duration fields then just use a helper column to strip the " h" off and then point the formula to the helper column
-
YES!!!! I was looking for an Average IF type command but somehow I missed it! You rock Matt! Thank you!
-
We do have d in that column but it doesn't seem to be causing any problems with the calculation. Thank you!
-
Ok, now one more question...is there a way for me to send the values from the Summary sheet to another sheet? I know how to do cell links and formulas that reference another sheet, but I need to take these values from 6 sheet summaries and then average them all together to then display that final number on a dashboard, but none of the ways I know to pull data allow me to grab the data from the Summary fields...
-
Ooh, nevermind...sheet summary report. Duh! I'm all good
-
Deleted, didn't read the existing response well enough
-
AVERAGEIF works if you only have one range/criteria set for evaluation. If (in the future) you need to add to it, you will need to change over to
=AVG(COLLECT(..................))
The "d" should not affect anything if it is in a duration column set up using the dependencies/Gantt. If you did not have those on and they were just text values then you would have to incorporate a way to remove those.
-
Thanks Paul! I do currently only have one range/criteria set but this is great to know for the future because I have no doubt this customer is going to want more and more magic from me!!
And yes, this is in the duration column auto-created by Gantt so the "d" does not seem to be causing any problems.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!