I am currently working on a spreadsheet to calculate values depending on whether or not they meet specific criteria.
I need a formula to workout whether or not there are any 3 FTE's within 300 days of other, as this is the criteria that I require.
This box is a simplified version of the main criteria, below I have put the entire formula that I use.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Source[/TD]
[TD]School[/TD]
[TD]Field 1[/TD]
[TD]Field 2[/TD]
[TD]Field 3[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]ABC High [/TD]
[TD]12/09/2013[/TD]
[TD]8[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]ABC High[/TD]
[TD]30/05/2014[/TD]
[TD]2[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]PERM[/TD]
[TD]A[/TD]
[TD]ABC High[/TD]
[TD]06/06/2014[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]SEN[/TD]
[TD]B[/TD]
[TD]BCD High[/TD]
[TD]09/06/2014[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]B[/TD]
[TD]Not at school[/TD]
[TD]10/08/2014[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]BCD High[/TD]
[TD]04/08/2014[/TD]
[TD]8[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
Currently, I use this formula to workout whether or not a value should be counted. But what I need is a way of working out if any 3 FTE values are within 300 days of each other (threshold), so I can then count the all FTE as events.
=(SUM(IFERROR(1/COUNTIFS($I$112:$I$161,$I$112:$I$161,$E$112:$E$161,">="&"41518",$E$112:$E$161,"<="&"41639",$I$112:$I$161,"<>P20000000",$D$112:$D$161,"<>0",$C$112:$C$161,"<>0",$B$112:$B$161,"=FTE",$E$112:$E$161,"<>0", $F$112:$F$161, "<>0"),0)))
I think that maybe I might have to have a separate column to have an indicator that I can then just put an extra COUNTIF criteria in to look for a value.
Any help on this issue would be greatly appreciated!
I need a formula to workout whether or not there are any 3 FTE's within 300 days of other, as this is the criteria that I require.
This box is a simplified version of the main criteria, below I have put the entire formula that I use.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Source[/TD]
[TD]School[/TD]
[TD]Field 1[/TD]
[TD]Field 2[/TD]
[TD]Field 3[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]ABC High [/TD]
[TD]12/09/2013[/TD]
[TD]8[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]ABC High[/TD]
[TD]30/05/2014[/TD]
[TD]2[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]PERM[/TD]
[TD]A[/TD]
[TD]ABC High[/TD]
[TD]06/06/2014[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]SEN[/TD]
[TD]B[/TD]
[TD]BCD High[/TD]
[TD]09/06/2014[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]B[/TD]
[TD]Not at school[/TD]
[TD]10/08/2014[/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]FTE[/TD]
[TD]A[/TD]
[TD]BCD High[/TD]
[TD]04/08/2014[/TD]
[TD]8[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]
Currently, I use this formula to workout whether or not a value should be counted. But what I need is a way of working out if any 3 FTE values are within 300 days of each other (threshold), so I can then count the all FTE as events.
=(SUM(IFERROR(1/COUNTIFS($I$112:$I$161,$I$112:$I$161,$E$112:$E$161,">="&"41518",$E$112:$E$161,"<="&"41639",$I$112:$I$161,"<>P20000000",$D$112:$D$161,"<>0",$C$112:$C$161,"<>0",$B$112:$B$161,"=FTE",$E$112:$E$161,"<>0", $F$112:$F$161, "<>0"),0)))
I think that maybe I might have to have a separate column to have an indicator that I can then just put an extra COUNTIF criteria in to look for a value.
Any help on this issue would be greatly appreciated!