How do you create an "AverageIf" formula for more than 1 criteria in the same column?

JayneB

Hi Team,

I am using a formula calculation sheet to try to work out how to create a formula to average %s in a Smartsheet where the Line of Business Column (in some cases) has more than 1 Business unit e.g. Sales and Sales (Local Sales).

I have tried Average If functions, AvgCollect, AverageAnd, but have had no luck. I just want the average of the 2 averages below, but would love it in one formula based on the master Smartsheet to make sure its accurate.

E.g.

Any help would be greatly appreciated

Faaez Kamaal

I don't use the parent/child hierarchy. So, shooting from the hip on this one.

Would =AVG(CHILDREN([FY21 Average]1),CHILDREN([FY21 Average]2)) work for you? Change the numbers to match your row numbers.

JayneB

Thank you for sharing, however sadly it did not, it came up with an error message:

Paul Newcome

I would just average the cells directly.

=AVG([FY21 Average]5, [FY21 Average]6)

JayneB

Thanks Paul, for some reason this doesn't come to the same average as the total column average. However there are some texts cells within the list so I am wondering if this is creating the discrepancy between the averages:

From the master sheet:

Avg of the avgs (top row):

I did another test with another group where there was a bigger gap:

From the master sheet:

Avg of the avgs (top row):

Paul Newcome

What are your existing formulas for the other individual rows that you are wanting to get the average of?

JayneB

Hi Paul,

=AVERAGEIF({Line of Business}, [Primary Column]@row, {Year Data})

Paul Newcome

Try this:

=AVERAGEIF({Line of Business}, OR(@cell = [Primary Column]5, @cell = [Primary Column]6), {Year Data})

(or whatever your row numbers are)