Calculating Pace of Campaign

[auero]

Hi All,

I'm finally surrendering for assistance. I've been a long time lurker and this forum has been a an amazing resource .

I'm trying to calculate the pace of a digital campaign I'm running based on the current date. Below is a sample of what I'm trying to implement. I am not attempting to calculate the current fulfillment which would be ~20%.

This would based on the current date (4/6/18)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Impression Goal[/TD]
[TD]Served Impressions[/TD]
[TD]Pace[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]4/1/18[/TD]
[TD]4/30/18[/TD]
[TD]10,000[/TD]
[TD]1,998[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

Is what I'm trying to do possible?

 

[mikerickson]

I'm not sure how you are calculating pace, but something like this might help.
This is a linear target curve. If you are using a different calculation method, what is it and could we get some examples of inputs and expected outputs.

Unknown
	A	B	C	D	E	F	G	H	I
1	Client	Start Date	End Date	Impression Goal	Served Impressions	Pace		intermediate date	intermediate target
2	ABC	4/1/18	4/30/18	10000	1998	1		4/6/18	2000.00
Sheet1

 

[auero]

This is very helpful!

I used the "Pace" value as a placeholder of what value it should be displaying based on the current date. Currently, I'm unable to calculate this column.

I'm basically trying to reproduce the "On Schedule Indicator" or OSI from here: http://www.adopsinsider.com/ad-ops-...ery-ad-ops-department-should-start-their-day/

They use =IF(Current Date > End Date, ((Delivery To Date / (End Date – Start Date)) * (End Date – Start Date)) / (Goal), ((Delivery To Date / (Current Date – Start Date)) * (End Date – Start Date)) / (Goal)) however I cannot get it to work. This article is older, which is why it might not be functioning properly.

 

[mikerickson]

That formula looks like it uses Named Ranges.

 

[thisoldman]

Try this.

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl67"] A
[/TD]
[TD="class: xl67, width: 73"] B [/TD]
[TD="class: xl67, width: 73"] C [/TD]
[TD="class: xl67, width: 64"] D [/TD]
[TD="class: xl67, width: 75"] E [/TD]
[TD="class: xl67, width: 64"] F [/TD]

[TD="class: xl67"] 1
[/TD]
[TD="class: xl68"]Current Date[/TD]
[TD="class: xl69, align: right"]04/07/2018[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67"] 2 [/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67"] 3 [/TD]
[TD="class: xl68"]Client[/TD]
[TD="class: xl70"]Start Date[/TD]
[TD="class: xl70"]End Date[/TD]
[TD="class: xl70"]Goal[/TD]
[TD="class: xl70"]Impressions[/TD]
[TD="class: xl70"]Pace[/TD]

[TD="class: xl67"] 4 [/TD]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl69, align: right"]04/01/2018[/TD]
[TD="class: xl69, align: right"]04/30/2018[/TD]
[TD="class: xl71, align: right"]10,000[/TD]
[TD="class: xl71, align: right"]1,998[/TD]
[TD="class: xl72, align: right"]96.6%[/TD]

</tbody>

F3, as direct adaptation of the formula posted above:
=IF($B$1>C4,(E4/(C4-B4))*(C4-B4)/D4,(E4/($B$1-B4))*(C4-B4)/D4)

F3, alternative:
=(C4-B4)*E4/D4/IF($B$1>C4,C4-B4,$B$1-B4)