Help required for Analysing large data in a pivot table

[Scrumpywill]

hello could anyone help with this please

i have a large customer list and I want to analyse which customers are down on spend month by month year I’ve year and show on a graph

i can do all of the above but the graph is too busy and it’s hard to see the results- out of the list I only want to see the customers that are down this year compared to last year

thank for your help

cheers steve

 

[oldbrewer]

	cust1	cust2	cust3	cust4	cust5
01/01/2017	1859	2663	232	4094	1171
01/02/2017	886	1110	969	1430	3344
01/03/2017	2264	3707	4933	84	4309
01/04/2017	2310	2330	4429	1368	1406
01/05/2017	2294	1639	4362	1579	4951
01/06/2017	3297	684	2354	1893	2460
01/07/2017	4547	123	612	3330	3700
01/08/2017	2680	2172	3435	652	288
01/09/2017	4863	4853	547	1505	1498
01/10/2017	4377	578	3634	4521	246
01/11/2017	1277	2712	2308	545	32
01/12/2017	1580	191	181	1707	539
01/01/2018	78	4110	4811	3349	1279			78 3349 red
01/02/2018	370	618	3458	4521	2165			370 618 2165 red
01/03/2018	4034	3448	4013	1590	4402			3448 4013 red
01/04/2018	1387	1390	4640	2420	2375			1387 1390 red
01/05/2018	4761	2830	3906	1002	4946			3906 1002 4946 red
01/06/2018	4738	4641	2121	3672	1500			2121 1500 red
01/07/2018	716	2180	1571	1235	2894			716 1235 2894 red
	comparing a month with the same month last year is statistically suspect
	you need to determine if sales figures are varying NORMALLY
	or if there are SPECIAL CAUSES in play
	the technique that can be used is statistical process control
	since I generated the numbers using the RAND function
	none are really significantly different
	if you post a few real sets of data I will analyse them for you

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[Scrumpywill]

Hello,

unable to share the data due to GDPR

 

[oldbrewer]

you could anonymise them a bit eg customer1 customer2

 

[Scrumpywill]

you could anonymise them a bit eg customer1 customer2

Hello,

If I have the customers in column A and years in b , c,d would you be able to sort a formula to compare the 3 values and highlight the sales drop off in column c with is year 2017/2018

thanks again Steve

 

[oldbrewer]

customer	2016_2017	2017_2018	marker
cust1	608	9966
cust2	513	8829
cust3	4838	4601	x
cust4	6639	4892	x
cust5	4798	6535
cust6	7591	4318	x
cust7	4752	9532
cust8	7551	1994	x
cust9	2036	9655
cust10	7274	9624
	is this what you mean
	the formula would be in cond formatting of course
	so cust3 , 4, 6 and 8 would be red

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Scrumpywill]

[MENTION][/MENTION]

customer	2016_2017	2017_2018	marker
cust1	608	9966
cust2	513	8829
cust3	4838	4601	x
cust4	6639	4892	x
cust5	4798	6535
cust6	7591	4318	x
cust7	4752	9532
cust8	7551	1994	x
cust9	2036	9655
cust10	7274	9624
	is this what you mean
	the formula would be in cond formatting of course
	so cust3 , 4, 6 and 8 would be red

<tbody>
</tbody>

Spot on that would be very helpful

cheers Steve

 

[oldbrewer]

cond formatting formula in A2

=C2<B2

paint it down the column