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.
Posted on Sep 16, 2016 9:12 AM
I suggest adding a column that encodes the current ranking for each category average:
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
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:
Posted on Sep 16, 2016 12:07 PM




