4 Replies Latest reply: Apr 21, 2013 8:26 AM by littlebirdie
littlebirdie Level 1 Level 1 (0 points)

I am making a spread sheet with a total that with enteries substracts from it making a sub-total. In other words at the beginning I have $100 (whole pie chart in red) then for 10 seperate enteries I take away $10. Making sub-total substract by $10 each time and decreasing red pie chart by 10% each time in green. I can easily get the numbers spread sheet to work. What I am having trouble making work is the pie chart reflecting the difference from total and sub-total with each entry. I can only get 50% of the chart to change.

 

Thank you for the knowledge.


iMac, OS X Mountain Lion (10.8.3)
  • 1. Re: make a pie chart with working subtotal and total
    pjdube Level 3 Level 3 (830 points)

    Is it one pie chart or 10?

  • 2. Re: make a pie chart with working subtotal and total
    pjdube Level 3 Level 3 (830 points)

    Something like this?

     

    Screen Shot 2013-04-21 at 12.17.14 AM.JPG

     

    You can download it here.

     

    You can also select a pie and then go to the spreadsheet and move down the black box to get the different figures.

  • 3. Re: make a pie chart with working subtotal and total
    Barry Level 7 Level 7 (29,180 points)

    Hi lb,

     

    Pie charts show values totaling 100%. You start off with 100, or 100% available and 0 or 0% taken away.

     

    Each $10 entry subtracts 10 from the original 100. So after the first entry and subtraction you have 90 available and 10 taken, adding up to 100%.

    Picture 9.png

    As the entries continue, the green wedge grows larger. Here seven entries have been subtracted from the available funds. I've shown this image with the chart selected to show where it is getting the two numbers, and the labels ro the legend below the pie.

    Picture 8.png

     

    After the tenth entry you have subtracted all 100 and have 0 available, leaving a 100% green chart.

    Picture 6.png

     

    I used formulas to calculate the amount left in column B, and to keep the cells blank where an amount to be subtracted had not yet been entered in column C.

     

    B3: =IF(C>0,$B$2-SUM($C$2:C3),"")

    Fill down to B12.

     

    Formula in B13 and C13 calculate the amounts to be displayed on the chart.

     

    B13: =MAX(B2:B12)-SUM(C2:C12)

    C13: =SUM(C2:C12)

     

    To change the chart colours from the default blue and darker green, select the chart, then click on the coloured square above column B to select the 'blue' wedge, then click the Fill Color well in the format bar and choose a red from the palette. If you want a brighter green than the one assigned by Numbers to column C's part of the chart, click th coloured square above column C, and repeat the process.

     

    Regards,

    Barry

  • 4. Re: make a pie chart with working subtotal and total
    littlebirdie Level 1 Level 1 (0 points)

    Thank you, Thank you. This is what I was trying to accomplish. I simplified the problem to be able to be more specific. I adjusted the formulas and was able to produce what I was trying. The part that I was missing that you and "pjdude" made apparent was that to do this I needed to use 2 comumns and not just one.

     

    Muchly appreciated to you and "pjdude"