Theory Question

[Stephen_IV]

Can someone please explain to me why this formula works?

=1-(B2/$A$2)

Thanks
Stephen

Percentage of absences during summer school.xls
	A	B	C	D
1	Days	DaysAbsent	Percentage
2	21	0	100.00%
3		1	95.24%
4		2	90.48%
5		3	85.71%
6		4	80.95%
7		5	76.19%
8		6	71.43%
9		7	66.67%
10		8	61.90%
11		9	57.14%
12		10	52.38%
13		11	47.62%
14		12	42.86%
15		13	38.10%
16		14	33.33%
17		15	28.57%
18		16	23.81%
19		17	19.05%
20		18	14.29%
21		19	9.52%
22		20	4.76%
23		21	0.00%
Sheet2

 

[KleptoOne]

Because in order to figure the percentage of days to total days, you divid days by total days. This would give you the % of days missed,
To flip it to show the % of days not missed, you subtract from 1.

 

[jproffer]

The division (B2/$A$2) yields a decimal less than 1 (the $ signs keep the second reference on A2 instead of dragging it down thru the column). So 1 minus this decimal yields another decimal less than 1. For instance if the days absent are 7, the formula alone yields 0.3333333333. One minus this is 0.6666666666, and formatted as a percentage it's 66.666666...or 66.67 as you show. Basically if you entered the number of days attended, instead of the days absent, you could take out the "1-", because the percentage you want is the days they WERE there, the "1-" just inverts it from days missed to days present.

EDIT: late again...lol, oh well now you have 2 answers.

 

[Artemus]

The formula shows the percentage of days Not Absent. A percentage is the ratio of something compared to 100. Formatting a number in Excel as a percent will cause the actual result of the formula to be multiplied by 100 and displayed with the % symbol.

In this case the 1 (when multiplied by 100) represents the 100% attendance that would result if there were no days of absence. The remainder of the formula -(B2/$A$2) represents the portion of time absent. 0/21 = 0. 0x100 = 0. 100-0=100.

Is this what you were interested in?

 

[Andrew Fergus]

Hi Stephen
Column B shows the absent days but column C shows the attendance percentage. B2/A2 gives a value less than 1 which you have deducted from 1 in column C. If you remove the format percentage from column C it will show values between 0 and 1, inclusive. As KleptoOne stated, you have effectively condensed a number of calculations into a single formula. Try using -1 or 22 in column B to see the effect on column C.
HTH, Andrew.

 

[Stephen_IV]

Thanks everyone for the help!!!


Stephen