Help with Formulas in MS Word

[philiporchard]

I really need some help please with a document I'm producing to help staff calculate an overall level for a child in maths.

Maths is made up of 5 areas:

Using & Applying = 20%
Number = 25%
Calculating = 25%
Shape, space, measure = 20%
Handling Data = 20%

Levels for each area would range from 1c which is the lowest to 5a, being the highest. i.e a child may be 3c in Using & Applying and 4a in Handling data. Each level such as 3c has a certain points value. E.g.

1C 7
1b 9
1A 11
2C 13
2B 15
2A 17
3C 19
3B 21
3A 23
4C 25
4B 27
4A 29
5C 31
5B 33
5A 35

An example.
A teacher gives the following levels to a child:

3A Using & Applying
3B Number
3A Calculating
3C Shape, space, measure
4A Handling Data

The table I need to produce must take all those levels and first convert it to points. It must then adjust the value taking into account the percentage value. i.e Handling data is only worth 10%.

The table must then total the points and convert it back into a level, such as 4c.

All help appreciated

 

[bensd]

Do the 5 areas of maths always make 100%?!

 

[retired_member34]

Wrote a long post then Firefox crashed! Aargh. A condensed version:

Not my field but the table formulae in Word look a bit basic, limited to simple math functions. It would do the job if you entered the points, but not the scores, and they also seem to not auto-recalculate. So I would suggest VBA or an embedded Excel worksheet. VBA will do it, but it has been a few years since I used it so will not try to advise! Excel is easy.

Embed a worksheet, then setup two ranges 15 rows by 2 columns, with the level/point data. In the first put the levels in the first column (sorted ascending) with the points in the second (similar to how you have listed in your post, but sorted), in the second put the points in the first column (sorted ascending) and the levels in the second. These are your two lookup tables to convert from level to points and vice versa.

In the cell you want to present the overall level sum the lookup points for each of the individual levels, multiplied by the weighting, then lookup again the weighted total points to get the overall level.

So something like:

=vlookup(weighted_total,second_lookup_table,2)

where

weighted_total=vlookup(u&a_level,first_lookup_table,2)*0.2+vlookup(n_level,first_lookup_table,2)*0.25+vlookup(c_level,first_lookup_table,2)*0.25+vlookup(ssm_level,first_lookup_table,2)*0.2+vlookup(hd_level,first_lookup_table,2)*0.1

(I am assuming there is an error in the first part of your post and Handling Data is worth 10% of the total, not 20%). Apologies if there is an element of sucking eggs here, you may well be able to do it in Excel and wanted to know how to do it in Word! In which case I think VBA.