JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,687
- Office Version
- 365
- Platform
- Windows
I have a table of date that I want to process in several different ways. Some of these ways are very similar so I made a second table on the same sheet. In the example below, the table on the left (SchoolW1) compares a weighted average as computed by a UDF to a simple average. The table on the right (SchoolW2) shows that the weights (above the tables in yellow) do not need to add up to 1. They get the same result as long as they have the same relative values. I made the body of SchoolW2 equal to that of SchoolW1 by simply typing "=" in each of the top cells in SchoolW1 and then clicking on the corresponding cells in SchoolW1 and then copying down.
I have a couple of questions:
I have a couple of questions:
- Is there an easier or better way to do this?
- I tried to make another table on a different sheet using the same process, but it didn't work. Is there a way to do that?
- Most importantly, is there a way to create a multiple copies of some data that are all interconnected so that any changes made to any of them will be reflected in all of the others?
Weighted Averages.xlsx | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
4 | Weights | 10% | 10% | 20% | 10% | 10% | 40% | 100% | Weights | 2 | 2 | 4 | 2 | 2 | 8 | 20 | |||||||
5 | Student | #1 | #2 | Mid Term | #3 | #4 | Final Exam | Weighted Average | Simple Average | Δ | Student | #1 | #2 | Mid Term | #3 | #4 | Final Exam | Weighted Average | Simple Average | Δ | |||
6 | Amy | 100 | 100 | 100 | 100 | 100 | 100 | 100.00 | 100.00 | =0.00 | Amy | 100 | 100 | 100 | 100 | 100 | 100 | 100.00 | 100.00 | =0.00 | |||
7 | Joe | 75 | 75 | 100 | 75 | 75 | 100 | 90.00 | 83.33 | +6.67 | Joe | 75 | 75 | 100 | 75 | 75 | 100 | 90.00 | 83.33 | +6.67 | |||
8 | Molly | 88 | 89 | 85 | 91 | 87 | 89 | 88.10 | 88.17 | -0.07 | Molly | 88 | 89 | 85 | 91 | 87 | 89 | 88.10 | 88.17 | -0.07 | |||
9 | Bill | 100 | 100 | 75 | 100 | 100 | 75 | 85.00 | 91.67 | -6.67 | Bill | 100 | 100 | 75 | 100 | 100 | 75 | 85.00 | 91.67 | -6.67 | |||
10 | Ruth | 50 | 60 | 70 | 80 | 90 | 100 | 82.00 | 75.00 | +7.00 | Ruth | 50 | 60 | 70 | 80 | 90 | 100 | 82.00 | 75.00 | +7.00 | |||
11 | Pete | 0 | 95 | 97 | 0 | 96 | 94 | 76.10 | 63.67 | +12.43 | Pete | 0 | 95 | 97 | 0 | 96 | 94 | 76.10 | 63.67 | +12.43 | |||
12 | Mike | 100 | 90 | 80 | 70 | 60 | 50 | 68.00 | 75.00 | -7.00 | Mike | 100 | 90 | 80 | 70 | 60 | 50 | 68.00 | 75.00 | -7.00 | |||
School Exams Wtd |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I4 | I4 | =SUM(TestWeights) |
T4 | T4 | =SUM(TestWeightsEqual) |
I6:I12 | I6 | =wtdavg(SchoolW1[@['#1]:[Final Exam]],TestWeights) |
J6:J12 | J6 | =SUM(SchoolW1[@['#1]:[Final Exam]])/COUNT(SchoolW1[@['#1]:[Final Exam]]) |
K6:K12,V6:V12 | K6 | =[@[Weighted Average]]-[@[Simple Average]] |
N6:N12 | N6 | =SchoolW1[@['#1]] |
O6:O12 | O6 | =SchoolW1[@['#2]] |
P6:P12 | P6 | =SchoolW1[@[Mid Term]] |
Q6:Q12 | Q6 | =SchoolW1[@['#3]] |
R6:R12 | R6 | =SchoolW1[@['#4]] |
S6:S12 | S6 | =SchoolW1[@[Final Exam]] |
T6:T12 | T6 | =wtdavg(SchoolW2[@['#1]:[Final Exam]],TestWeightsEqual) |
U6:U12 | U6 | =SUM(SchoolW2[@['#1]:[Final Exam]])/COUNT(SchoolW2[@['#1]:[Final Exam]]) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'School Exams Wtd'!TestWeights | ='School Exams Wtd'!$C$4:$H$4 | I4, I6:I12 |
'School Exams Wtd'!TestWeightsEqual | ='School Exams Wtd'!$N$4:$S$4 | T4, T6:T12 |