Domain Manage

Help with Formulas in MS Word

Discussion in 'General Board' started by philiporchard, Jan 7, 2012.

Thread Status:
Not open for further replies.
  1. philiporchard United Kingdom

    philiporchard Well-Known Member

    May 2007
    Likes Received:
    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 ;)
  2. Domain Forum

    Acorn Domains Elite Member

    Likes Received:
  3. bensd United Kingdom

    bensd Well-Known Member Exclusive Member

    Jan 2007
    Likes Received:
    Do the 5 areas of maths always make 100%?!
  4. retired_member34

    retired_member34 Retired Member

    Jul 2008
    Likes Received:
    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:




    (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.
Thread Status:
Not open for further replies.

Share This Page