Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Excel issue - formula incorrect?

  • 20-11-2008 11:39AM
    #1
    workaccount
    Closed Accounts Posts: 2,460 ✭✭✭


    Hi,
    I have a column in Excel with information. See this first link
    excel1ea9.th.jpgthpix.gif

    I am trying to create a pie chart from the essential column. There are four 1 values and one 2 value. I want the pie-chart to show 80% for the 1's and 20% for the 2's. Thus the pie-chart should only have two colours but it looks like this....
    excel2xc6.th.jpgthpix.gif


    The logic for the pie chart is =SERIES(,,TabName!$C$3:$C$7,1)


    Any help appreciated.


Comments

  • #2
    rockal
    Registered Users, Registered Users 2 Posts: 757 ✭✭✭rockal


    Create a Unique Item List

    1) Select C3:C7 and then go to Data>Filter>Advanced Filter

    2) Ensure "Action" is set to Copy list to another location

    3) "List Range" is $C$3:$C$7 and "Criteria Range" is blank.

    4) "Copy to" is $F$1 (or any single cell in row 1).

    5) Check the "Unique records only" checkbox and then click "Ok".

    Now, rename cell F1 (or the cell you chose), you have a list containing only one occurrence of each item in the list beginning at F2


    Enter the COUNTIF Formula

    Now we have the unique list, we can now count how many times each item appears in our original list in C1:C7. So in cell G2 (G1 is a heading and can read "Count") enter the COUNTIF Formula as shown below

    =COUNTIF($C$3:$C$7,$F2)

    Now click the Fill Handle of G2 while it's selected, or copy down manually. Note the use of the absolute range $C$3:$C$7 and the absolute column and relative row of $F2.

    Then do your chart based on column 'G'


Advertisement