Hi, could you pls help me. (You will see I am just learning the basics. )
1) I got a huge database (LIST1) and a small excel spreadsheet (LIST2) connected by e-mail address:
LIST1 headers and content (this is the main data source):
account e-mail | order id | sales | units
xxx@ gmail.com 123456 500 6
yyy@ gmail.com 234567 350 4
zzz@ exple.com 345678 200 1
vvv@ exple.com 456789 600 2
www@ anythg.com 56789 500 3
LIST2 headers and content:
account e-mail | rep-cust
xxx@ gmail.com REP
www@ anythg.com REP
those who are in LIST1 but NOT in LIST2 are considered as "Pure Customers"
those who are in both lists are considered as "Representatives"
in the example: xxx@ and www@ are REPs, the rest is Pure Customer.
I want to see the share of sales by Pure custs and Reps. I know it is very simple but am not able to write the correct IF formula to see the results. Reps are OK, but Pure Customers are marked with "Null", but it looks dummy in the chart.
What I simply wrote was:
IF [rep-cust]=NULL THEN "Pure Customer" ELSE "Representative"
I also tried
IF [rep-cust]="REP" THEN "Representative" ELSE "Pure Customer"
2) my chart problem: I have campaign numbers in a database like: 201701, 201702, .... , 201712, 201801, 201802, 2018... (stored as numbers), and although they have 12 campaigns/year, these are not equal to the real months of the year (e.g. campaign 201805 starts on 27 April and ends on 30 May). I would like to make a line chart, but if I put campaign number to columns, the chart does not appear because of the number format. I would also like to compare data to previous year in the line chart. Do I have to define campaign in a calculated field, is this the only way to show data by campaign in chart?
Can you provide a sample workbook with make up data?