Pull in average of column for certain rows in sheet

lindsay.greenstone83846

I have a tracker setup that I track support requests. I have a few metrics that allow me to see AVE SLA time to completion and assignment. The problem I am having is I want to show this same AVE SLa time value BUT only for specific rows based on a value in one of my columns. I seem to have a mental block on how I can accomplish this and am hoping it is something that is simple I am overlooking. 

 

Here is the AVE formula used: =AVG({Request Tracker Range 2})

I want to pull that average still but only for rows in my tracker that have a "Request Type" (column Name) of "Business Value Add Project". I also want to pull in all rows that don't use this "Request Type" too. 

L_123

avg(collect({Request Tracker Range 2},{Request Tracker Request Type},@cell = "Business Value Add Project"))

 

don't understand what you mean by "don't use request type too" but the above will get you the avg where request type is business value add project if you set the request type range correctly.

lindsay.greenstone83846

thank you for the help! I attempted to utilize this formula and it returns an error message (message attached from my metrics sheet I am utilizing in a dashboard).

Here is what I updated the formula too:

 

=AVG(COLLECT({2019 SFDC Request Tracker Range 1}, {Request Tracker Range 4 - Request Type}, @cell = "Business Value Add Project"))

 

L_123

I believe your issue is with your ranges. Are they both column references or are they a fixed range? If they are fixed ranges you need to make sure they have the same amount of cells in them.

 

For testing you can try switching out avg with join and see what is being averaged.

Paul Newcome

The AVG function will only work if there are no blanks or text within the range. You may need to repeat the range you want to average within the COLLECT function and specify with an ISNUMBER function.

 

=AVG(COLLECT({2019 SFDC Request Tracker Range 1}, @{2019 SFDC Request Tracker Range 1}, ISNUMBER(@cell), {Request Tracker Range 4 - Request Type}, @cell = "Business Value Add Project"))

 

L@123 is also correct about making sure the ranges are the same size, but that should throw an #INCORRECT ARGUMENT SET error.

 

Here is some verbiage on the #INVALID COLUMN VALUE error. Basically you are trying to display a date in a text/number column or something similar to that.

 

#INVALID COLUMN VALUE

Cause

The formula contains or references a data type that is inconsistent with the column type where it is inserted. For example, this MAX formula is placed in a Text/Number column and references other values in the Date column...

=MAX([Due Date]1, [Due Date]52)

...If the column that contains the formula is not a Date column type, the latest date in the range cannot be returned. (See the MAX Function Help article for more information on how the MAX function works.)

Resolution

Make one of the following adjustments to your sheet:

• Type the formula in a different column, one where its type aligns with the type of value that the formula is expected to return.
• Right-click the column header at the top of the column and select Edit Column Properties. Change the column to a type that aligns with the type of value that the formula is expected to return.
• Add IFERROR to your function such that it returns a different value if it encounters the #INVALID COLUMN VALUE error, for example: =IFERROR(“Not a number!”, MAX([Due Date]1, [Due Date]5))
• Wrap your formula in a function that can convert it to the expected data type. For example: =WEEKNUMBER(MAX([Due Date]1, [Due Date]5) when placed in a Text/Number column returns a number that corresponds with the highest date’s week number out of a 52 week period.