Formula Help

[saiyamvora13]

Hello,
I needed some help with a few formula's. Here is my data.
Per Advertiser ID, I need to find
[TABLE="width: 334"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Total requests sent with user data[/TD]
[/TR]
[TR]
[TD]Percent requests sent with user data[/TD]
[/TR]
[TR]
[TD]Bid volume[/TD]
[/TR]
[TR]
[TD]Bid rate[/TD]
[/TR]
[TR]
[TD]Avg bid value (multiply your answer by 1000 to get to CPM)[/TD]
[/TR]
[TR]
[TD]Win volume[/TD]
[/TR]
[TR]
[TD]Win rate[/TD]
[/TR]
[TR]
[TD]Impressions[/TD]
[/TR]
[TR]
[TD]Avg spend (multiply your answer by 1000 to get to CPM)

All help is highly appreciated[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1415"]
<colgroup><col span="7"><col><col span="6"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]advertiser_id[/TD]
[TD]site_id[/TD]
[TD]domain[/TD]
[TD]datacenter[/TD]
[TD]geo[/TD]
[TD]user_accepts_cookies?[/TD]
[TD]user_has_data?[/TD]
[TD]impressions[/TD]
[TD]spend[/TD]
[TD]requests_sent[/TD]
[TD]bid_volume[/TD]
[TD]total_bid_value[/TD]
[TD]total_wins[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 1[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]564[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 1[/TD]
[TD]DC 2[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]762[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 2[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5.305[/TD]
[TD="align: right"]8367[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14.5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 3[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 4[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.125[/TD]
[TD="align: right"]4109[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 5[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6.18[/TD]
[TD="align: right"]1853[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 6[/TD]
[TD]DC 2[/TD]
[TD]ca[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 6[/TD]
[TD]DC 4[/TD]
[TD]ph[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 7[/TD]
[TD]DC 3[/TD]
[TD]ca[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 8[/TD]
[TD]DC 5[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 9[/TD]
[TD]DC 1[/TD]
[TD]ca[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 9[/TD]
[TD]DC 2[/TD]
[TD]ca[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 9[/TD]
[TD]DC 4[/TD]
[TD]au[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 9[/TD]
[TD]DC 4[/TD]
[TD]sg[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 10[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]27.611[/TD]
[TD="align: right"]31452[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]73.869[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 11[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 12[/TD]
[TD]DC 2[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.94[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 13[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.125[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]537073285[/TD]
[TD="align: right"]69302[/TD]
[TD]DOMAIN 13[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10.145[/TD]
[TD="align: right"]1511[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1415"]
<colgroup><col span="7"><col><col span="6"></colgroup><tbody>[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]18965[/TD]
[TD]DOMAIN 154[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]20001[/TD]
[TD]DOMAIN 1064[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41854[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]22520[/TD]
[TD]DOMAIN 1065[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]6.9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]30395[/TD]
[TD]DOMAIN 1066[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]30395[/TD]
[TD]DOMAIN 1067[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]5507[/TD]
[TD="align: right"]1650.442[/TD]
[TD="align: right"]399709[/TD]
[TD="align: right"]380860[/TD]
[TD="align: right"]114258[/TD]
[TD="align: right"]5705[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]30395[/TD]
[TD]DOMAIN 1067[/TD]
[TD]DC 2[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]345067[/TD]
[TD="align: right"]1893[/TD]
[TD="align: right"]567.9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]30395[/TD]
[TD]DOMAIN 1068[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]53058[/TD]
[TD]DOMAIN 154[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]61666[/TD]
[TD]DOMAIN 1069[/TD]
[TD]DC 3[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]61666[/TD]
[TD]DOMAIN 1070[/TD]
[TD]DC 2[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2783[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]61666[/TD]
[TD]DOMAIN 1071[/TD]
[TD]DC 2[/TD]
[TD]us[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]20150106[/TD]
[TD="align: right"]536871256[/TD]
[TD="align: right"]61666[/TD]
[TD]DOMAIN 1072[/TD]
[TD]DC 1[/TD]
[TD]us[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetat12]

This data set is perfect for a pivot table! However, pivot tables do have some restrictions and if you want more freedom in formatting i suggest using the sumifs formula or sumproduct.

 

[saiyamvora13]

How would I get the win rate and bid rate using a pivot table?
Would you be able to give me the formula?

 

[Tetat12]

You would add total_wins to the pivot table column twice and on the second column right click and hit show values as... % of grand total. is that how you are calculating win rate?

 

[saiyamvora13]

Bid rate needs to be a ratio of the number of bids compared to the number of qualified events to bid on.
Win rate would be a ration of the no of wins bids compared to the no of bids placed.