Formula

Scott Shutter

I am trying to add a metric on a sheet summary for campaign tracking that pulls from two options on a column dropdown. Option A is campaigns that are “In Development” and the other is “In-Market”. I want to get the average in another column that is “duration” of days for campaigns that are “In Development” and not “In Market”.

So, think the formula would start with “COUNTIF” and that would ID the two options In Development or In-Market and point to the In Development. Then I want to average just those days and when I add the AVG command, do I start the second half of the formula with “and” or how would I add that?

First attempt was -> =COUNTIF([Campaign Status]:[Campaign Status], = “In Development”)…. That’s where I am stuck. How do I add the second command for AVG? Or is there a better formula with simply AVG that will let me only average campaigns in development? 

Appreciate your consideration in advance.

Best - Scott

Mark Cronk

Hi Scott,

Try:

=AVERAGEIF([Campaign Status]:[Campaign Status], = “In Development”, [duration]:[duration])

Change my Duration placeholders to the name of your duration column that you want averaged.

Work?

Mark

Scott Shutter

Mark that also gets me the 'unparsable' message. The column name is Duration so that should not be an issue. The sheet is divided into two sets of rows, with the column "Campaign Status" used to identify them. The top half shows campaigns in development and the bottom In Market. Those in market only have task duration of ~1day. In Development campaigns run into weeks/months. Wanting to get the average time for campaigns in Development as a metric to track for capacity planning. Any thoughts on how to approach this or another formula?

Thank you so much for your help.

Scott

Mark Cronk

Hi Scott,

This formulas should give you what you're looking for:

=AVERAGEIF([Campaign Status]:[Campaign Status], “In Development”, Duration:Duration)

Your Duration column needs to be a number of days.

An unparsable error is usually a comma or parens out of place in the formula.

Work?

Mark

Scott Shutter

Mark here is what finally worked. Got this with our account success rep. =IFERROR(AVG(COLLECT({EMEA DE - Campaign Roadmap Update Range 1}, {EMEA DE - Campaign Roadmap Update Range 2}, "In Development", {EMEA DE - Campaign Roadmap Update Range 3}, [Vehicle Type]@row)), "0")

There were other issues with the averaging and the multiple values which is the additional formula you see.

I'm now trying to add an additional status to pickup here in addition to "In Development", namely "Complete", but can't get that to work. Tried : =IFERROR(AVG((COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "In Development", {BR - Campaign Roadmap Update Vehicle}, [Vehicle Type]@row)), (COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "Complete", {BR - Campaign Roadmap Update Vehicle}, [Vehicle Type]@row))), "0").

That isn't working either. For some reason, it drops the AVG for campaigns in Development and yeilds a "0" value which cannot be right. Hmmmm.

Thoughts?