Progression matrix
Many thanks
Regards
Nick
"Pete_UK" wrote:
Nick,
I've assumed that your data is in Sheet1, occupying A2 to B6, and that
you want a summary table in a different sheet (Sheet2), laid out so
that your headers are in B1:J1 and A2:A6. Put this formula in C3:
=SUMPRODUCT((Sheet1!$A$2:$A$6=$A3)*(Sheet1!$B$2:$B $6=C$1))
and copy across and down to occupy C3:J6. Of course, your real data
will occupy more than 6 rows in Sheet1, so adjust the ranges
accordingly.
If you want to avoid showing a lot of zeros, apply conditional
formatting to C3:J6 such that if Cell Value is 0 (zero), then set the
foreground colour to white.
Another way would be to use a Pivot Table, but then I'm a formula
man !!.
Hope this helps.
Pete
On Dec 8, 11:40 pm, Nick Horn
wrote:
Hi
I am using a large excel file, in both 2003 and 2007 versions. The data
contains exam results based on exams 6 years apart and looks like this.
KS2 Grade KS4 Grade
4 6
5 8
3 2
5 5
5 5
I need to produce a matrix which looks like this:
KS4 1 2 3 4 5 6 7 8
KS2
5 2 1
4 1
3 1
2
Any help is much appreciated with many thanks in anticipation.
Best wishes
Nick
|