ryansapp
Board Regular
- Joined
- Apr 22, 2013
- Messages
- 54
<!--StartFragment--> Needing a month-to-date conversion tracking formula (based oncampaign_name, date [A], and summation of clicks [C]), assume column A is spanning all days and months through the end of year. Simple enough, I know,..but for whatever reason I'm struggling with setting up the date range start/end for the formula.
Next, I need a date monthly cap metformula (referencing the cap amount [D], and then referencing the sumifformula I needed from previous request. When that # is achieved, factor in 'bonus' amount toformula [CAMPAIGN_BONUS*every click after click cap is met]). I'm fairly decent with some nested IFs but this one in particular has me dumbfounded and I know its not too intense for the experts!
<!--EndFragment-->Layout below for reference...
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]CAMPAIGN_NAME[/TD]
[TD]CLICKS[/TD]
[TD]MONTHLY CLICK CAP[/TD]
[TD]CAMPAIGN_BONUS[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6/1/17[/TD]
[TD]Cars[/TD]
[TD]25[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6/2/17[/TD]
[TD]Cars[/TD]
[TD]1300[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7/15/17[/TD]
[TD]Trucks[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]$5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7/16/17[/TD]
[TD]Cars[/TD]
[TD]900[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7/18/17[/TD]
[TD]Cars[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7/30/17[/TD]
[TD]Cars[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Next, I need a date monthly cap metformula (referencing the cap amount [D], and then referencing the sumifformula I needed from previous request. When that # is achieved, factor in 'bonus' amount toformula [CAMPAIGN_BONUS*every click after click cap is met]). I'm fairly decent with some nested IFs but this one in particular has me dumbfounded and I know its not too intense for the experts!
<!--EndFragment-->Layout below for reference...
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]CAMPAIGN_NAME[/TD]
[TD]CLICKS[/TD]
[TD]MONTHLY CLICK CAP[/TD]
[TD]CAMPAIGN_BONUS[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6/1/17[/TD]
[TD]Cars[/TD]
[TD]25[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6/2/17[/TD]
[TD]Cars[/TD]
[TD]1300[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7/15/17[/TD]
[TD]Trucks[/TD]
[TD]25[/TD]
[TD]5[/TD]
[TD]$5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7/16/17[/TD]
[TD]Cars[/TD]
[TD]900[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7/18/17[/TD]
[TD]Cars[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7/30/17[/TD]
[TD]Cars[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]$1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]