Create a lesson Graph

Wayne Contello Austin, Texas
You can achive this be creating a stacked bar graph where the segments show a percentage and you make several other changes to the default settings:
Set up the Lessons table as shown above. Then make a new 2x2 table where the first column is a header (I called this table "Lessons Summary"):
Now enter the formulas as follows:
B1=COUNTIF(Lessons::B2:B20, "=TRUE")
E2=COUNTA(Lessons::A2:A20)B1
To make the graph select A1 through B2:
Then select the stacked, horizontal, bar chart from the Charts Menu in the tool bar:
Now double click the left segment (in my example the Blue segment) of the bar chart and change the Fill color:
Now change the fill color of the other segment (to white). I have the change the font color when changing the segment color to make the text readable.
Now change the chart so it show "%" instead of values by selecting the chart, then then opening the Charts Inspector:
Then unselect all of the other check boxes in the "Value Axis (X)" menu as you don't want gridlines, tick marks, etc. Do the same for the "Category Axis (Y)".
Now select the chart and change the background fill color to "grey" so the white segment shows up

Like (0)


Wayne Contello wrote:
Now enter the formulas as follows:
B1=COUNTIF(Lessons::B2:B20, "=TRUE")
E2=COUNTA(Lessons::A2:A20)B1
Thanks for your time.
I did everything except I didn't understand how to do that part that is in the quote above.
I ended up with the graph and the "Lesson summary" and both are connected to each other.
The "Lessons" checklist is just siting there, I couldn't connect it to the rest.

Like (0)


HI,
There are no connections 'from' the Lessons table. It's simply a record of which lessons you've completed.
The two formulas:
=COUNTIF(Lessons::B2:B20, "=TRUE")
=COUNTA(Lessons::A2:A20)B1
count two things:
=COUNTIF(Lessons::B2:B20, "=TRUE"
goes in cell B2 of the Lesson Summary table. This formula counts the number of checked boxes in rows 2 to 20 of column B of the Lessons table. (In Wayne's example, three boxes are checked. so the formula returns the value 3.)
=COUNTA(Lessons::A2:A20)B1
goes in cell B3 of the Lesson Summary table. This formula counts the number of lessons entered in rows 2 to 20 or column A of the Lessons table (19) then subtracts the count of completed lessons in cell B1 of its own table (3) to determine the number of lessons not yet completed (16) and returns that value to its cell (B3)
Note that for the formula to work you must either name the main table "Lesons", or replace "Lessons" in both formulas with the name of your main table.
The numbers in the small table, Lesson Summary, will change as you complete further lessons and check them off using the checkboxes on the main table, Lessons.
Those changes will be shown in the cart, which will automatically calculate the percentage completed and still pending from the calculated values in the table Lesson Summary.
Regards,
Barry

Like (0)
