Add a row and totals based on changing cell values
This topic has been covered at least a few times, but I wasn't able to find
anything to answer my particular situation... I'm very new to functions and need to automate some tasks within an Excel file so all help is appreciated. In my spreadsheet, I have several columns of data, but the ones I need to reference and work with are as follows: -Column A (Individual Name) -Column F (Leader Name) -Column E (Date Graded) -Column G (Overall Grade) First, I need to sort the data ASCENDING by Column F, Column A, Column E (so by Leader Name, then Individual Name, then Date Graded) and there is a header row. Second, I need to insert a blank row when the value of Column A (Individual Name) changes and I need to add an average of the value in Column G (Overall Grade) for the rows that apply to the value in Column A (Individual Name). This is to provide an average grade for the individual. Obviously I don't want to do this going from Row 1 (Header Row) to Row 2 (Data Row). Third, I need to insert a blank row when the value of Column F (Leader Name) changes and I need to add an average of all of the Individuals 'assigned' to that Leader. This is basically a repeat of the second step above, but at a Leader level instead of an Individual level. So the basic concept here is I'm trying to get an average for each individual and a group average for each leader. As a bonus, I'd like to color code the new lines (the ones for the Individual grouping/average and the ones for the Leader grouping/average) and make the values on the new line all be bold font. Hope this makes sense - again, all help is greatly appreciated! |
Add a row and totals based on changing cell values
After sorting, select all of your data. Then use Data - Subtotals. This will
allow you to designate when breaks should occur, and what should happen at those breaks. Note that based on your hierarchy, you'll want to create the subtotals for the leaders first, and then the individuals (on second subtotal, uncheck option to replace previous subtotals). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul" wrote: This topic has been covered at least a few times, but I wasn't able to find anything to answer my particular situation... I'm very new to functions and need to automate some tasks within an Excel file so all help is appreciated. In my spreadsheet, I have several columns of data, but the ones I need to reference and work with are as follows: -Column A (Individual Name) -Column F (Leader Name) -Column E (Date Graded) -Column G (Overall Grade) First, I need to sort the data ASCENDING by Column F, Column A, Column E (so by Leader Name, then Individual Name, then Date Graded) and there is a header row. Second, I need to insert a blank row when the value of Column A (Individual Name) changes and I need to add an average of the value in Column G (Overall Grade) for the rows that apply to the value in Column A (Individual Name). This is to provide an average grade for the individual. Obviously I don't want to do this going from Row 1 (Header Row) to Row 2 (Data Row). Third, I need to insert a blank row when the value of Column F (Leader Name) changes and I need to add an average of all of the Individuals 'assigned' to that Leader. This is basically a repeat of the second step above, but at a Leader level instead of an Individual level. So the basic concept here is I'm trying to get an average for each individual and a group average for each leader. As a bonus, I'd like to color code the new lines (the ones for the Individual grouping/average and the ones for the Leader grouping/average) and make the values on the new line all be bold font. Hope this makes sense - again, all help is greatly appreciated! |
Add a row and totals based on changing cell values
Thanks Luke, that was a very simple resolution...I can't believe I've never
used that before - I will be from now on though! "Luke M" wrote: After sorting, select all of your data. Then use Data - Subtotals. This will allow you to designate when breaks should occur, and what should happen at those breaks. Note that based on your hierarchy, you'll want to create the subtotals for the leaders first, and then the individuals (on second subtotal, uncheck option to replace previous subtotals). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Paul" wrote: This topic has been covered at least a few times, but I wasn't able to find anything to answer my particular situation... I'm very new to functions and need to automate some tasks within an Excel file so all help is appreciated. In my spreadsheet, I have several columns of data, but the ones I need to reference and work with are as follows: -Column A (Individual Name) -Column F (Leader Name) -Column E (Date Graded) -Column G (Overall Grade) First, I need to sort the data ASCENDING by Column F, Column A, Column E (so by Leader Name, then Individual Name, then Date Graded) and there is a header row. Second, I need to insert a blank row when the value of Column A (Individual Name) changes and I need to add an average of the value in Column G (Overall Grade) for the rows that apply to the value in Column A (Individual Name). This is to provide an average grade for the individual. Obviously I don't want to do this going from Row 1 (Header Row) to Row 2 (Data Row). Third, I need to insert a blank row when the value of Column F (Leader Name) changes and I need to add an average of all of the Individuals 'assigned' to that Leader. This is basically a repeat of the second step above, but at a Leader level instead of an Individual level. So the basic concept here is I'm trying to get an average for each individual and a group average for each leader. As a bonus, I'd like to color code the new lines (the ones for the Individual grouping/average and the ones for the Leader grouping/average) and make the values on the new line all be bold font. Hope this makes sense - again, all help is greatly appreciated! |
All times are GMT +1. The time now is 10:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com