Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summarising a List of Data
I am trying to partially summarise a list of costs so that all costs over a
certain amount cost are shown in detail while all other costs are grouped together as "others". for example repair to ac 30,000 repair to roof 14,000 new table 500 new chairs 450 Cleaning costs 200 Total 45,150 Would show as repair to ac 30,000 repair to roof 14,000 others 1150 Total 45,150 |
#2
|
|||
|
|||
Summarising a List of Data
I'd insert another column (D??) and use a formula:
=if(B21000,a2,"Others") and drag down. Then you could either sort your data by that column and do: data|subtotals If you had multiple "repair to ac"'s, you could use the outlining symbol to the left to show all the details, then come back and hide the details for "Others". Or you could create a pivottable (data|pivottable) to summarize your data. Philip wrote: I am trying to partially summarise a list of costs so that all costs over a certain amount cost are shown in detail while all other costs are grouped together as "others". for example repair to ac 30,000 repair to roof 14,000 new table 500 new chairs 450 Cleaning costs 200 Total 45,150 Would show as repair to ac 30,000 repair to roof 14,000 others 1150 Total 45,150 -- Dave Peterson |
#3
|
|||
|
|||
Summarising a List of Data
To expand on what Dave said, you could also use SUMIF on your data range to
only grab the numbers <1,000 for "others", and then apply conditional formatting to the range so that if they are summed in "others," they show up purple (or the like) "Dave Peterson" wrote: I'd insert another column (D??) and use a formula: =if(B21000,a2,"Others") and drag down. Then you could either sort your data by that column and do: data|subtotals If you had multiple "repair to ac"'s, you could use the outlining symbol to the left to show all the details, then come back and hide the details for "Others". Or you could create a pivottable (data|pivottable) to summarize your data. Philip wrote: I am trying to partially summarise a list of costs so that all costs over a certain amount cost are shown in detail while all other costs are grouped together as "others". for example repair to ac 30,000 repair to roof 14,000 new table 500 new chairs 450 Cleaning costs 200 Total 45,150 Would show as repair to ac 30,000 repair to roof 14,000 others 1150 Total 45,150 -- Dave Peterson |
#4
|
|||
|
|||
Summarising a List of Data
Many thanks
"Dave Peterson" wrote: I'd insert another column (D??) and use a formula: =if(B21000,a2,"Others") and drag down. Then you could either sort your data by that column and do: data|subtotals If you had multiple "repair to ac"'s, you could use the outlining symbol to the left to show all the details, then come back and hide the details for "Others". Or you could create a pivottable (data|pivottable) to summarize your data. Philip wrote: I am trying to partially summarise a list of costs so that all costs over a certain amount cost are shown in detail while all other costs are grouped together as "others". for example repair to ac 30,000 repair to roof 14,000 new table 500 new chairs 450 Cleaning costs 200 Total 45,150 Would show as repair to ac 30,000 repair to roof 14,000 others 1150 Total 45,150 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Val list Excel 97 fire macro | Excel Discussion (Misc queries) | |||
Expanding Data validation from List | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) |