VBA Teachers FTE

[anthonyexcel]

I need some help. I am trying to caluate a teachers FTE. Basically if a teacher is at a school for the whole day then tthe teacher gets 0.2 FTE, if the teacher is at the school for a half a day then the teacher gets 0.1 FTE. In the example below if there is 1 school in a cell then it is worth 0.2 for that school, if there are 2 schools in a cell then each school is worth 0.1.

******** ******************** src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js%22%3E%3C/SCRIPT%3E%3CCENTER%3E%3Ctable" target=_blank>http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

Microsoft Excel - FTE Example.xlsx ___Running: 14.0 : OS = Windows XP <TBODY> </TBODY>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout <FORM name=formCb059465><INPUT value="Copy Formula" type=button name=btCb290334 *******='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);'></FORM> <TBODY> </TBODY>
<SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION selected value=Ford>A1</OPTION></SELECT> = <INPUT value="Emp ID" size=80 name=txbFb965317> <TBODY> </TBODY>
	<CENTER>A</CENTER>	<CENTER>B</CENTER>	<CENTER>C</CENTER>	<CENTER>D</CENTER>	<CENTER>E</CENTER>	<CENTER>F</CENTER>
<CENTER>1</CENTER>	Emp ID	Mon	Tue	Wed	Thu	Fri
<CENTER>2</CENTER>	103	Washington	Madison	Washington	Barnum	Barnum,Washington
<CENTER>3</CENTER>	104	Nixon,Madison	Madison,Nixon	Barnum,Nixon	Nixon,Barnum	Jackson
<CENTER>4</CENTER>	105	Harrison	Harrison	Harrison		Harrison
<CENTER>5</CENTER>	106	Lincoln, Harrison	Harrison	Lincoln	Ford, Harrison	Ford
Sheet1 <TBODY> </TBODY>

<TBODY>
</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


This is what I am looking for on sheet 2:

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

Microsoft Excel - FTE Example.xlsx ___Running: 14.0 : OS = Windows XP <TBODY> </TBODY>

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp [URL="http://www.mrexcel.com/forum/#javascript<strong></strong>:void(0)"](A)bout <TBODY>

<TBODY>

<FORM name=formCb658016><INPUT value="Copy Formula" type=button name=btCb074442 *******='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);'></FORM> </TBODY>
<SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190><OPTION selected value=0.3>A1</OPTION></SELECT> = <INPUT value=Washington size=80 name=txbFb428726> <TBODY> </TBODY>
	<CENTER>A</CENTER>	<CENTER>B</CENTER>	<CENTER>C</CENTER>	<CENTER>D</CENTER>	<CENTER>E</CENTER>	<CENTER>F</CENTER>	<CENTER>G</CENTER>	<CENTER>H</CENTER>
<CENTER>1</CENTER>	Washington	Madison	Barnum	Nixon	Jackson	Harrison	Lincoln	Ford
<CENTER>2</CENTER>	0.5	0.2	0.3
<CENTER>3</CENTER>		0.2	0.2	0.4	0.2
<CENTER>4</CENTER>						0.8
<CENTER>5</CENTER>						0.4	0.3	0.3
Sheet2 <TBODY> </TBODY>

</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


Thank in advance.[/URL]

 

[Andrew Poulsom]

Formula in B8 copied down and across:

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Emp ID	Mon	Tue	Wed	Thu	Fri
2	103	Washington	Madison	Washington	Barnum	Barnum,Washington
3	104	Nixon,Madison	Madison,Nixon	Barnum,Nixon	Nixon,Barnum	Jackson
4	105	Harrison	Harrison	Harrison		Harrison
5	106	Lincoln,Harrison	Harrison	Lincoln	Ford,Harrison	Ford
6
7		Washington	Madison	Barnum	Nixon	Jackson	Harrison	Lincoln	Ford
8	103	0.5	0.2	0.3
9	104		0.2	0.2	0.4	0.2
10	105						0.8
11	106						0.4	0.3	0.3

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula B8 =SUM(COUNTIF($B2:$F2,B$7),COUNTIF($B2:$F2,"*"&B$7&"*"))*0.1 <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[anthonyexcel]

Thanks Andrew. That did the trick. I appreciate your help! Thanks again!