Excel data analytics through creative formatting ?

[kvazar]

Hey guys, so I have a pivot that looks something like this:


Name | Class | Apples | Oranges | Pears | Cranberries
----|-----|------|-------|-----|-----------
Andy | 2015 | 1 | 2 | 3 | 4
Bill | 2015 | 2 | 3 | 2 | 2
Bobby | 2015 | 1 | 3 | 3 | 3
Chester | 2015 | 3 | 1 | 2 | 4
Gerard | 2014 | 2 | 1 | 2 | 2
Milford | 2014 | 2 | 2 | 1 | 2
Albert | 2014 | 2 | 1 | 4 | 2
Stoner | 2014 | 2 | 1 | 3 | 2
Priyanka | 2014 | 2 | 2 | 3 | 2
Willow | 2014 | 3 | 2 | 2 | 3
Yoggi | 2014 | 3 | 2 | 2 | 2




Where:


1 - Chose


2 - Likes, but didn't chose


3 - Doesn't like


4 - Never tried


Yes. that's already a pivot. As the data for each 'food' was initially from different files (status on Apples, on Oranges etc.).


So now I want to create an analysis from this table to know:
what is the rate of people choosing one option over another if both options are 'liked', what is the rate of people liking "A" if they like "B". How many other options are liked by kids who chose "A". (I pretty much need each vs. each table that will be updated if the table is changed)


And I also want to see a progression of all of this over the years.


Which approach will you recommend? I initially used this data to create another pivot table, but it doesn't allow to put Options to the Row and Column at the same time, so I'm a bit confused. Any ideas?

 

[kvazar]

I'll ne happy to comment more or maybe just tell me the directions - what should I try to do? I can do all the rest.

 

[kvazar]

Can't edit the initial post, but here's what I mean

how to get from this:

Name	Class	Apples	Oranges	Pears	Cranberries
Andy	2015	1	2	3	4
Bill	2015	2	3	2	2
Bobby	2015	1	3	3	3
Chester	2015	3	1	2	4
Gerard	2014	2	1	2	2
Milford	2014	2	2	1	2
Albert	2014	2	1	4	2
Stoner	2014	2	1	3	2
Priyanka	2014	2	2	3	2
Willow	2014	3	2	2	3
Yoggi	2014	3	2	2	2

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

To this:

(How many people chose one item over another if they liked both and chose one of them)

	Apples	Oranges	Pears	Cranberries
Apples		1/4	0/1	0/0
Oranges	3/4		2/3	3/3
Pears	1/1	1/3		1/1
Cranberries	0/0	0/3	0/1

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