1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MOS exam practice - subtotals question

Discussion in 'Microsoft Office Specialist (MOS)' started by Kathie, Apr 17, 2012.

  1. Kathie

    Kathie New Member

    3
    0
    1
    Hi - hoping someone can help please! I'm doing some practice tests for the MOS Excel 2007 exam, and this question has me stumped.

    The data is presented as per the attached (I've just hidden some rows in the attached to avoid replicating lots of figures). View attachment Subtotal query.xls

    The question is phrased as:- 1. Show the subtotal of Men, Women and Children's sales, with page breaks between groups. Make sure to replace current subtotals and show the summary below the data.

    I thought I knew how to do subtotals, but I don't understand this - what are the 'groups' that the question is referring to? With the data for Men/Women/Children in separate columns, and the dates all as unique values, there's nothing to group! :cry:
     
  2. Coupe2T

    Coupe2T Megabyte Poster

    590
    43
    67
    I suspect it means the groups of Men, Women and Children. Not that you have to group subtotals, just that you show subtotals, with page breaks between the groups.

    So I suspect they are looking to see something like

    Men = Subtotal

    Women = Subtotal

    Children = Subtotal

    Although I have not done the MOS exams, so could be completely wrong. Sure someone else will advise before too long, but the above is how I read it.
     
    Certifications: ECDL, Does that Count!?!
  3. Kathie

    Kathie New Member

    3
    0
    1
    Thanks Coupe2T - but how to I get that result using the Subtotal command rather than just typing a formula? ('cos I'm sure that's what they want me to do, hence the stuff about replacing current totals and adding page breaks!). In the "At each change in" box it's only giving me the option of Date, Men, Women or Children (and leaving it blank is not possible), and because all the data values are unique, it puts a subtotal and page break after every row!
     
    Last edited: Apr 17, 2012
  4. Coupe2T

    Coupe2T Megabyte Poster

    590
    43
    67
    It's been a long time since I did anything EXCEL myself so I can't really remember, but I think you have to group the data or something like that, then you can use the subtotal command to pull data from groups. If that makes sense. In hindsight, maybe that's more what they meant by grouping.

    ie, you group the column of all the data for men, then you can use the subtotal command to pull that data down and summarize it (Although I couldn't tell you the command). Or something to that effect anyway.

    I may be leading you slightly down a blind alley as like I say, been a long time. Just google groups and subtotal command in Excel or something, i'm sure that will turn up some good info.
     
    Certifications: ECDL, Does that Count!?!
  5. soundian

    soundian Gigabyte Poster

    1,460
    71
    107
    Last edited: Apr 17, 2012
    Certifications: A+, N+,MCDST,MCTS(680), MCP(270, 271, 272), ITILv3F, CCENT
    WIP: Knuckling down at my new job
  6. soundian

    soundian Gigabyte Poster

    1,460
    71
    107
    View attachment Subtotal query.xls

    That's my best effort. I can't think of any other way to do it.

    Edit. Aaargh. The original was .xls, not xlsx. Anyway, imagine page breaks are there.
     
    Last edited: Apr 17, 2012
    Certifications: A+, N+,MCDST,MCTS(680), MCP(270, 271, 272), ITILv3F, CCENT
    WIP: Knuckling down at my new job
  7. Kathie

    Kathie New Member

    3
    0
    1
    I've had a reply from the practice test provider! (credit to them). The intention is that you just use the Subtotals command even though the result is a 'subtotal' (and page break) after each of the 35 rows of data that just replicates the data in the row above! What a pointless and stupid thing to ask someone to do, even as an exercise - I hope the exam isn't like that!
     
  8. soundian

    soundian Gigabyte Poster

    1,460
    71
    107
    The only other option I could think of was that it was a pointless and stupid question. Actually, it wasn't so much the question, it was the data.
     
    Certifications: A+, N+,MCDST,MCTS(680), MCP(270, 271, 272), ITILv3F, CCENT
    WIP: Knuckling down at my new job

Share This Page

Loading...