Converting a letter to a number
Posted by Mark Taylor on July 05, 2001 6:14 AM
I am putting together a spreadsheet on the absence of members of staff. For information purposes instead of just putting a number for absenties I want to put a letter instead, i.e. 'h' for holiday, 's' for sick etc.
The value of any letter entered will be '1'.
So the line at the end of the workbook that is calculating the total number of days off that someone has had will calculate the letters as '1' etc
Please help asap
Posted by test on July 05, 2001 6:26 AM
Posted by Scott on July 05, 2001 6:28 AM
Mark,
You might be able to enter the formula "=counta(a2:h2)" assuming that a2:h2 is you data fields, and that for any days that they work, the cell is left blank. This will give you a total count of how many days off.
Posted by Aladin Akyurek on July 05, 2001 7:54 AM
Lets say the letters of interest are "h" and "s" in the range A1: A10 where you enter data concerning the staff members.
1) =COUNTIF(A1:A10,"a")+COUNTIF(A1:A10,"s")
or
2) =SUM((A1:A10={"a","s"})+0) [ This one is an array formula, so you need to hit CONTROL+SHIFT+ENTER at the same time (not just enter) to enter it. ]
both will give you a total count.
You can extend each of these formulas if there are other letters of which any instance must be counted as 1.
Aladin
