+ Reply to Thread
Results 1 to 27 of 27

% of column and % of grand total?

  1. #1
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    % of column and % of grand total?

    Hi,

    I've attached a sheet and just realised the figures aren't quite right. I would like the boy and girl column to read percentage of boys that got the question correct and percentage of girls that got the question correct. However the grand total should be percentage of children overall that got the question correct if that makes sense?

    When I have it as % of grand total the table reads correctly but the graph is way off...

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,006

    Re: % of column and % of grand total?

    No sample attached. Try re-uploading it.

  3. #3
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Oh sorry. It was too big so I've zipped it.
    Attached Files Attached Files

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: % of column and % of grand total?

    In what way is the graph way off? It's plotting the data from the table. Are you thinking it can somehow stack 33% and 40% and get a total of 38% (for not attempted)?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Yeah, I suppose I want the graph to plot the grand total column and show what division that is between boys and girls. Is that possible?

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: % of column and % of grand total?

    If your pivot table layout is fairly static, you could use a helper table alongside and plot that chart from that?

  7. #7
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    A helper table? Ok, never heard of them, I'll Google it and see what comes up!

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: % of column and % of grand total?

    It's just a table of formulas alongside your pivot table to calculate the correct boy/girl percentages of the grand totals and then plot those as a chart.

  9. #9
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Ok, thanks. I think a PivotTable may be the wrong tool for the job - I'll try to recreate this analysis with formulas...

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: % of column and % of grand total?

    is that what you want?

    but PowerQuery is required
    Attached Files Attached Files
    Last edited by sandy666; 03-26-2018 at 06:34 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  11. #11
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Am I missing an attached Sandy?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: % of column and % of grand total?

    There is an attachment - a .zip file.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: % of column and % of grand total?

    Quote Originally Posted by CraigMcKee View Post
    Am I missing an attached Sandy?
    No, you don't - just refresh thread

  14. #14
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Thank you - I could read your reply but couldn't see the attachment for some reason Got it now, I'll have a look. Thanks.

  15. #15
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: % of column and % of grand total?

    I don't know why but forum doesn't allow to upload xlsb file but it should (less than 10 MB) so maybe you check post before second attempt with a zip

  16. #16
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Hi Sandy, no it's not. I want the table to read percentage of boys and girls that got it correct but as a percentage of boys and girls. I would like the chart to show overall percentage of children that got it correct with a boy girl breakdown if that makes sense.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: % of column and % of grand total?

    I grew up not far away from English Martyrs'. This is a public forum, and I am not sure they would like to see their data published here. You really ought to remove it and provide dummy, desensitised data instead, what with data protection, GDRP and all that.
    Last edited by AliGW; 03-26-2018 at 07:15 AM.

  18. #18
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: % of column and % of grand total?

    like this or I misunderstood

    school.jpg
    '
    Clipboard01.jpg

    maybe show the result what you want to achieve. Do that manually if necessary

    attach source tab and result tab only. You don't need to attach whole file

  19. #19
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157
    Ali it is made up data. Nothing in there that is true at all. Just picked some random school names to test my data. Sandy, I'll send something back as soon as I'm back at my laptop - thank you.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: % of column and % of grand total?

    OK, well consider that those 'random' schools might not be very happy!!!

    Just use School A, School B, etc.

  21. #21
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    I've attached a screen shot of what I'm trying to achieve.

    https://snag.gy/qh8s6v.jpg

    The table shows the data relating to boy girl then I've created columns g - i which show the scores as a % of the overall figure - otherwise if all boys and all girls were correct then this could potentially read 200%!

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: % of column and % of grand total?

    That is how I would do it. Unless your tables are going to change layout a lot, it's as easy a way as any, I think.

  23. #23
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    I've done it this way now creating a new PivotTable that I'll hide.

    I'm going to have a different sheet for every question so it is possible to copy this sheet with all the PivotTables and just change them to question 2 rather than question 1 without having to go thorugh all the calculations and formatting options for each one?

  24. #24
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: % of column and % of grand total?

    Yes. You could also just have one pivot table for all the questions and then link each table as required using GETPIVOTDATA formulas.

  25. #25
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: % of column and % of grand total?

    Hm, I feel a little
    How you get Boy/Girl/Incorrect = 0 ?
    Could you show the process of your calculation?

  26. #26
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: % of column and % of grand total?

    Sandy - no children got that one incorrect. GetPivotData - thank you, I'll take a look. I sometimes wish I'd never delved into the murky depths of PivotTables !

  27. #27
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: % of column and % of grand total?

    from your source table it appears that Incorrect for: Boy = 2, Girl = 1
    Nevermind, if you found a solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help creating pivot - grand total and grand total as % of column value
    By obionenairobi in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-18-2017, 08:00 AM
  2. Adding a formula column based on the pivot table's Grand Total column
    By trisoldee in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-26-2014, 05:29 PM
  3. Specifying one column in the grand total row in a pivot table
    By Phixer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2014, 12:35 PM
  4. Pivot Table (Need :- % of Grand Total Column)
    By sam99 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 12-20-2013, 03:56 AM
  5. formula calculates ratio for Grand Total, not Sub Total for each column
    By vkievsky in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2013, 11:26 AM
  6. [SOLVED] Is it possible to have both Grand Total and Grand Average in a Pivot Table?
    By ANS in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 10-16-2012, 05:15 AM
  7. Grand total in same column
    By emina002 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2011, 09:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1