0

Referring to the image below. How to put percentage (%) of presence & absence based on cells from Sat to Thur, if cell value is 1 it means presence else it is an absence.

enter image description here

3 Answers 3

4

Other way of doing it can be using count and countif functions,

=COUNTIF(A2:G2,"=1")/COUNT(A2:G2)

Putting the above formula in Present column and the one below in Absent column.

=COUNTIF(A2:G2,"=0")/COUNT(A2:G2)

then after that you can hit CTRL+SHIFT+5 to change to percentage format of this decimal values.

Assuming your records are starting with row number 2, in case if its different then change the formula number accordingly in formula. e.g. A2 will become A3, if its third row.

Note: 0 means absent and 1 means present, also the above formula works fine if there are non blank cells with numeric data contians values of 0 and 1.

2
  • 2
    Note that using COUNT in the denominator gives a number that may be different from (the hardcoded) 7, which may be desired. On the other hand, it does not count empty or non-numeric cells, and this may give an undesired effect. Use COLUMNS instead. Dec 3, 2013 at 16:30
  • Thanks for insight, +1 for that , I have added the note in the answer to clarify.
    – PKumar
    Dec 4, 2013 at 4:11
3

maybe you can try this :

=SUM(A1:G1)/7
to the Present column and

=1-J1
to the Absent column.

Then you have to change number format of present and absence column using Percentage. but it's only work if value of the presence is 1 or 0, maybe someone else can help you with better answer. thanks

Note : A1:G1 = Cell value that you need to calculate 7 = How many cell J1 = cell Present column

2

In the present field you can use =SUM(A1:G1)/7 * 100 and in the absent column use =SUM(100-H1) with A1:G1 being your days and H1 being your Present column.

This would also allow you to use a 0 or any letter/word to represent an Absent day. (i.e. you could use H for a holiday and it would count correctly towards your absent check)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .