+ Reply to Thread
Results 1 to 4 of 4

Summarising a List of Data

  1. #1
    Philip
    Guest

    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. #2
    Dave Peterson
    Guest

    Re: Summarising a List of Data

    I'd insert another column (D??) and use a formula:

    =if(B2>1000,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. #3
    FinRazel
    Guest

    Re: 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(B2>1000,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. #4
    Philip
    Guest

    Re: Summarising a List of Data

    Many thanks

    "Dave Peterson" wrote:

    > I'd insert another column (D??) and use a formula:
    >
    > =if(B2>1000,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 to Thread

Thread Information

Users Browsing this Thread

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

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