MOS exam practice - subtotals question

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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!

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.

 

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!

 

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.

 

Calculating subtotals and working with levels in Excel - Excel - Office.com

that has options at the bottom for all you need, page breaks, replacing totals and summarising below the data

EDIT: whoops, that's 2003. that's what you're looking for though.


Further edit: Ah, I see your problem.

 

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.

 

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!

 

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.