Hello, I need help formatting a pivot table with the same rule for different columns, using above average formula, see example data below -
I can get the above average across all columns, however need it to look at each column as an individual.
<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;">
<col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;">
<col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" span="3">
<tbody>
</tbody>
I can get it to do it for one column -
<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;">
<col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;">
<col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" span="3">
<tbody>
</tbody>
but when I try to expand the range it obviously doesn't work.
Below is the result I need to see -
<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;">
<col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;">
<col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" span="3">
<tbody>
</tbody>
I can get the above average across all columns, however need it to look at each column as an individual.
Row Labels | A | B | C | D |
1 | 2490 | 23377 | 17750 | 14564 |
2 | 2490 | 23377 | 17750 | 14563 |
3 | 2791 | 30008 | 20977 | 23999 |
4 | 2490 | 23377 | 17750 | 14564 |
5 | 2490 | 23377 | 17750 | 14564 |
6 | 2816 | 32742 | 19428 | 18641 |
7 | 2816 | 32742 | 19428 | 18641 |
8 | 2816 | 32741 | 19428 | 18642 |
9 | 2946 | 31642 | 20899 | 19837 |
10 | 3266 | 41168 | 21920 | 22082 |
11 | 3298 | 46605 | 25356 | 24314 |
12 | 3635 | 51916 | 26102 | 30000 |
13 | 3266 | 41168 | 21919 | 22082 |
14 | 3665 | 49457 | 23364 | 24863 |
15 | 3238 | 42597 | 20881 | 24629 |
16 | 3238 | 42596 | 20881 | 24630 |
17 | 3238 | 42597 | 20881 | 24629 |
18 | 3500 | 51270 | 27399 | 32133 |
19 | 3500 | 47404 | 21359 | 27093 |
20 | 3500 | 47403 | 21359 | 27093 |
21 | 3641 | 49386 | 21333 | 27892 |
22 | 3700 | 47404 | 21359 | 27093 |
23 | 3501 | 51613 | 22860 | 30296 |
24 | 3501 | 51612 | 22859 | 30295 |
25 | 3501 | 51612 | 30000 | 30295 |
26 | 3917 | 54654 | 24475 | 33991 |
27 | 3917 | 54654 | 24475 | 33991 |
28 | 3917 | 54654 | 24475 | 33992 |
29 | 3917 | 54654 | 24475 | 33991 |
30 | 3917 | 54654 | 24475 | 33990 |
31 | 3676 | 44530 | 24109 | 32951 |
32 | 3222 | 37550 | 18926 | 26655 |
33 | 3222 | 37551 | 18927 | 26655 |
34 | 3222 | 37551 | 18926 | 26655 |
35 | 3222 | 37550 | 18926 | 26654 |
36 | 2978 | 28095 | 18658 | 20377 |
37 | 2978 | 28095 | 18658 | 20377 |
38 | 2978 | 28095 | 18659 | 20377 |
39 | 3241 | 30621 | 19471 | 26049 |
40 | 3095 | 21497 | 18322 | 15139 |
41 | 3095 | 21497 | 18322 | 15139 |
42 | 3095 | 21497 | 18321 | 15140 |
43 | 3095 | 21497 | 18321 | 15139 |
44 | 3095 | 21498 | 18322 | 15139 |
45 | 3355 | 23902 | 20209 | 15522 |
46 | 3300 | 25360 | 23917 | 16769 |
47 | 3300 | 31130 | 26448 | 29064 |
48 | 3300 | 31130 | 26448 | 29063 |
49 | 2769 | 15009 | 16413 | 11555 |
50 | 3014 | 19503 | 17111 | 13747 |
51 | 3014 | 19502 | 17111 | 13746 |
52 | 3014 | 19502 | 17111 | 13746 |
I can get it to do it for one column -
Row Labels | A | B | C | D |
1 | 2490 | 23377 | 17750 | 14564 |
2 | 2490 | 23377 | 17750 | 14563 |
3 | 2791 | 30008 | 20977 | 23999 |
4 | 2490 | 23377 | 17750 | 14564 |
5 | 2490 | 23377 | 17750 | 14564 |
6 | 2816 | 32742 | 19428 | 18641 |
7 | 2816 | 32742 | 19428 | 18641 |
8 | 2816 | 32741 | 19428 | 18642 |
9 | 2946 | 31642 | 20899 | 19837 |
10 | 3266 | 41168 | 21920 | 22082 |
11 | 3298 | 46605 | 25356 | 24314 |
12 | 3635 | 51916 | 26102 | 30000 |
13 | 3266 | 41168 | 21919 | 22082 |
14 | 3665 | 49457 | 23364 | 24863 |
15 | 3238 | 42597 | 20881 | 24629 |
16 | 3238 | 42596 | 20881 | 24630 |
17 | 3238 | 42597 | 20881 | 24629 |
18 | 3500 | 51270 | 27399 | 32133 |
19 | 3500 | 47404 | 21359 | 27093 |
20 | 3500 | 47403 | 21359 | 27093 |
21 | 3641 | 49386 | 21333 | 27892 |
22 | 3700 | 47404 | 21359 | 27093 |
23 | 3501 | 51613 | 22860 | 30296 |
24 | 3501 | 51612 | 22859 | 30295 |
25 | 3501 | 51612 | 30000 | 30295 |
26 | 3917 | 54654 | 24475 | 33991 |
27 | 3917 | 54654 | 24475 | 33991 |
28 | 3917 | 54654 | 24475 | 33992 |
29 | 3917 | 54654 | 24475 | 33991 |
30 | 3917 | 54654 | 24475 | 33990 |
31 | 3676 | 44530 | 24109 | 32951 |
32 | 3222 | 37550 | 18926 | 26655 |
33 | 3222 | 37551 | 18927 | 26655 |
34 | 3222 | 37551 | 18926 | 26655 |
35 | 3222 | 37550 | 18926 | 26654 |
36 | 2978 | 28095 | 18658 | 20377 |
37 | 2978 | 28095 | 18658 | 20377 |
38 | 2978 | 28095 | 18659 | 20377 |
39 | 3241 | 30621 | 19471 | 26049 |
40 | 3095 | 21497 | 18322 | 15139 |
41 | 3095 | 21497 | 18322 | 15139 |
42 | 3095 | 21497 | 18321 | 15140 |
43 | 3095 | 21497 | 18321 | 15139 |
44 | 3095 | 21498 | 18322 | 15139 |
45 | 3355 | 23902 | 20209 | 15522 |
46 | 3300 | 25360 | 23917 | 16769 |
47 | 3300 | 31130 | 26448 | 29064 |
48 | 3300 | 31130 | 26448 | 29063 |
49 | 2769 | 15009 | 16413 | 11555 |
50 | 3014 | 19503 | 17111 | 13747 |
51 | 3014 | 19502 | 17111 | 13746 |
52 | 3014 | 19502 | 17111 | 13746 |
but when I try to expand the range it obviously doesn't work.
Below is the result I need to see -
Row Labels | A | B | C | D |
1 | 2490 | 23377 | 17750 | 14564 |
2 | 2490 | 23377 | 17750 | 14563 |
3 | 2791 | 30008 | 20977 | 23999 |
4 | 2490 | 23377 | 17750 | 14564 |
5 | 2490 | 23377 | 17750 | 14564 |
6 | 2816 | 32742 | 19428 | 18641 |
7 | 2816 | 32742 | 19428 | 18641 |
8 | 2816 | 32741 | 19428 | 18642 |
9 | 2946 | 31642 | 20899 | 19837 |
10 | 3266 | 41168 | 21920 | 22082 |
11 | 3298 | 46605 | 25356 | 24314 |
12 | 3635 | 51916 | 26102 | 30000 |
13 | 3266 | 41168 | 21919 | 22082 |
14 | 3665 | 49457 | 23364 | 24863 |
15 | 3238 | 42597 | 20881 | 24629 |
16 | 3238 | 42596 | 20881 | 24630 |
17 | 3238 | 42597 | 20881 | 24629 |
18 | 3500 | 51270 | 27399 | 32133 |
19 | 3500 | 47404 | 21359 | 27093 |
20 | 3500 | 47403 | 21359 | 27093 |
21 | 3641 | 49386 | 21333 | 27892 |
22 | 3700 | 47404 | 21359 | 27093 |
23 | 3501 | 51613 | 22860 | 30296 |
24 | 3501 | 51612 | 22859 | 30295 |
25 | 3501 | 51612 | 30000 | 30295 |
26 | 3917 | 54654 | 24475 | 33991 |
27 | 3917 | 54654 | 24475 | 33991 |
28 | 3917 | 54654 | 24475 | 33992 |
29 | 3917 | 54654 | 24475 | 33991 |
30 | 3917 | 54654 | 24475 | 33990 |
31 | 3676 | 44530 | 24109 | 32951 |
32 | 3222 | 37550 | 18926 | 26655 |
33 | 3222 | 37551 | 18927 | 26655 |
34 | 3222 | 37551 | 18926 | 26655 |
35 | 3222 | 37550 | 18926 | 26654 |
36 | 2978 | 28095 | 18658 | 20377 |
37 | 2978 | 28095 | 18658 | 20377 |
38 | 2978 | 28095 | 18659 | 20377 |
39 | 3241 | 30621 | 19471 | 26049 |
40 | 3095 | 21497 | 18322 | 15139 |
41 | 3095 | 21497 | 18322 | 15139 |
42 | 3095 | 21497 | 18321 | 15140 |
43 | 3095 | 21497 | 18321 | 15139 |
44 | 3095 | 21498 | 18322 | 15139 |
45 | 3355 | 23902 | 20209 | 15522 |
46 | 3300 | 25360 | 23917 | 16769 |
47 | 3300 | 31130 | 26448 | 29064 |
48 | 3300 | 31130 | 26448 | 29063 |
49 | 2769 | 15009 | 16413 | 11555 |
50 | 3014 | 19503 | 17111 | 13747 |
51 | 3014 | 19502 | 17111 | 13746 |
52 | 3014 | 19502 | 17111 | 13746 |