Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel issue - formula incorrect?

Options
  • 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

  • Options
    #2
    rockal
    Registered Users 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