00:00I have to say this is the ultimate guide for creating your very own attendance tracker and
00:06you can also sell it in Etsy if you like. So let's get started. You're going to select the area
00:11or
00:11the cells where you're going to insert your checkbox to and then go to insert under controls
00:17you're going to click on this checkbox here and that's going to introduce a checkbox to you and
00:22if you look at it when you put a check here it returns a true if you remove it it's
00:26going to
00:26return a false value here. Now to calculate the percent present you have to be familiar with two
00:32formulas. The first formula is count if and the first argument in there would be from cell C6 to G6
00:39essentially the first row itself comma and basically since you're calculating the percentage of present
00:46you're going to return look for true and what that does is basically counts the number of checkbox
00:51that has a check in it. Let's remove this. The second formula is the count A function and then
00:59the argument for count A would be the same range of C6 to G6 and close parenthesis and hit enter
01:05and
01:05what this formula does is counts the number of non-empty cells in the range of C6 to G6. Now
01:12armed with these two functions you can actually calculate the percentage using say count if again
01:18one more time the same set of formulas C6 to G6 and basically looking for true value and then you're
01:24going to take that count which is number of checkbox that's checked divided by the number of non-empty
01:32cells in the same range which is C6 to G6 like this close parenthesis and hit enter. Now the value
01:40is
01:40presented as a fraction all you have to do to convert that into percentage is go to the home on
01:45the ribbon
01:45and then go to under numbering change it to percentage and it shows you in percentage. You can
01:50apply the same formula right across every rows like this and then after that you can see anytime put a
01:56check here the number of present increases accordingly. Now to calculate the percent absent you're going to
02:04copy the same formula on your clipboard like this and this time around that's what we're going to do
02:09is we're going to highlight all the absent like this so that we can apply the formula in one go.
02:14Let me show
02:15how it's done. Once that's done you're going to paste the same formula as present but over here we're
02:20interested on non-checked value so essentially you're going to say false here. Now you're going to press
02:26ctrl enter to apply the same formula right across and while you still have this selected like this
02:32or all the absent column here you're going to go to home and the number you're going to basically
02:38change this to percentage and then job done.
Comments