formatting and equations

Question

Hi! I am hoping to make my work a little easier.&nbsp; I have an excel worksheet I am using to get data from but in order to do I am needing to do a lot of steps.&nbsp; &nbsp; Here is an example of my worksheet, there is additional info in the other boxes but not relevant to my work.&nbsp;&nbsp;&nbsp;I am trying to figure out the percentage of how many numbers on the right are under 180.&nbsp;&nbsp;This is what I am doing now...I am inserting 2 rows after each change in number in the first box, then copying and pasting under the column on the right an equation but each time have to remember what the starting number is for that group of rows.&nbsp; Here is the equation I am pasting =COUNTIFS(J2:J17,"&gt;69",J2:J17,"&lt;181") and&nbsp;=COUNT(J16487:J16672)&nbsp; &nbsp;It knows the last one is the one above but now where it started.&nbsp; Is there a way to automatically insert this empty rows between and a better way or automatic way to do my equation?&nbsp;&nbsp;&nbsp;Help me find an easier way!&nbsp; My worksheet has 29247 rows, so many lines to go through and the groups of each number (on the right) vary from 40-600s,&nbsp;3333&nbsp;&nbsp;&nbsp;&nbsp;90&nbsp;3333&nbsp;&nbsp;&nbsp;&nbsp;80&nbsp;333&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;444&nbsp;&nbsp;&nbsp;&nbsp;170&nbsp;444&nbsp;&nbsp;&nbsp;&nbsp;123&nbsp;444&nbsp;&nbsp;&nbsp;&nbsp;90&nbsp;444&nbsp;&nbsp;&nbsp;&nbsp;190&nbsp;6666&nbsp;&nbsp;&nbsp;&nbsp;200&nbsp;6666&nbsp;&nbsp;&nbsp;&nbsp;150&nbsp;666&nbsp;&nbsp;&nbsp;&nbsp;176&nbsp;666&nbsp;&nbsp;&nbsp;&nbsp;88&nbsp;666&nbsp;&nbsp;&nbsp;&nbsp;90&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;67&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;180&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;150&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;111&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;123&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;125&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;135&nbsp;777&nbsp;&nbsp;&nbsp;&nbsp;150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

jkpieterse · Answer

EmilyDyer&nbsp;I think this can be done using a pivot table.&nbsp;Make sure your data has a row with unique headings. Then press control+T to format the range as a table (check the "My table has headings" check-box).Click Insert, Pivot tableDrag the category column to the Rows areaDrag the column with the numbers to the rows areaRight-click that last added column in your pivot table and choose "Group". Set it to this:&nbsp;Drag that same field into the Values areaRight-click inside that area in the pivot table and choose "Show Values As", "% of parent row total".I end up with this:&nbsp;

Forum Discussion

formatting and equations

1 Reply

3333					90
3333					80
333					110
444					170
444					123
444					90
444					190
6666					200
6666					150
666					176
666					88
666					90
777					67
777					180
777					150
777					111
777					123
777					125
777					135
777					150