MD_Gene

Q: Pie Chart references not working

I have a table with a column of averages in column P.  The labels for the averages are in column B.  I click C to insert the chart, then pie chart, then add data.  I click "wedges" to allow me to identify the rows in the that I want to be included in the chart.  I choose the first three rows out of column P.  The "Values" text box shows the reference to the cells as in "Table1:P2,Table1:p3..."  But when I click the check mark, the references become "#ref".  See before and after below.  As always, your help is appreciated.

Screen Shot 2016-09-16 at 12.06.48 PM.pngScreen Shot 2016-09-16 at 12.07.00 PM.png

Posted on Sep 16, 2016 9:12 AM

Close

Q: Pie Chart references not working

  • All replies
  • Helpful answers

  • by MD_Gene,

    MD_Gene MD_Gene Sep 16, 2016 10:42 AM in response to MD_Gene
    Level 1 (20 points)
    Desktops
    Sep 16, 2016 10:42 AM in response to MD_Gene

    Perhaps I should explain what I am trying to do.  I have a long list of expenses (35 items).   They are listed in column B top to bottom in alphabetic order.  The average for each is in column P.  I sort the expense types by average per month.  I want to create a pie chart showing the top 10 expense items.  Any and all help is greatly appreciated.

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Sep 16, 2016 12:07 PM in response to MD_Gene
    Level 6 (19,282 points)
    iWork
    Sep 16, 2016 12:07 PM in response to MD_Gene

    I suggest adding a column that encodes the current ranking for each category average:

    Screen Shot 2016-09-16 at 2.00.24 PM.png

    P2=RANK(O2,O,0)

     

    this is shorthand for…select cell C2, then type (or copy and paste from here) the formula:

    =RANK(O2,O,0)

     

    select cell P2, copy

    select cells C2 thru the end of column P, paste

     

     

     

     

    Screen Shot 2016-09-16 at 2.04.37 PM.png

     

    Now create a second table (show in the previous screenshot as table "Top 10 Expenses")

    the first row AND the first column are headers

     

    A2=OFFSET(Expenses::A$1,MATCH(ROW()−1,Expenses::P)−1,0,rows,columns)

    B2=VLOOKUP(A2, Expenses::A:P, 15, 0)

    select cells A2 and B2, copy

    select cells A2 thru the end of column B, paste

     

    now select cells B2 thru the end of column (or a total of 10 cells), then select the pie chart from the chart menu:

    Screen Shot 2016-09-16 at 2.07.31 PM.png

  • by MD_Gene,

    MD_Gene MD_Gene Sep 16, 2016 12:54 PM in response to Wayne Contello
    Level 1 (20 points)
    Desktops
    Sep 16, 2016 12:54 PM in response to Wayne Contello

    Thanks Wayne.  I appreciate you sharing your expertise.

  • by MD_Gene,

    MD_Gene MD_Gene Sep 16, 2016 1:45 PM in response to Wayne Contello
    Level 1 (20 points)
    Desktops
    Sep 16, 2016 1:45 PM in response to Wayne Contello

    I got it to work pretty quickly.  Not sure how the "MATCH" function works in this regard but it works and I will investigate that at a later time.   I do enjoy learning this stuff coming from Excel and it's sure a lot easier with the mentoring of experts.  Thanks again.

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 16, 2016 2:03 PM in response to MD_Gene
    Level 6 (19,282 points)
    iWork
    Sep 16, 2016 2:03 PM in response to MD_Gene

    Gene,

     

    match searches for text in a column (or row) and returns the number of the element where the text was found (if it is found).