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.
<tbody>
</tbody>
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.
Type | Source | School | Field 1 | Field 2 | Field 3 |
FTE | A | ABC High | 12/09/2013 | 8 | - |
FTE | A | ABC High | 30/05/2014 | 2 | - |
PERM | A | ABC High | 06/06/2014 | - | - |
SEN | B | BCD High | 09/06/2014 | - | |
CME | B | Not at school | 10/08/2014 | - | |
FTE | A | BCD High | 04/08/2014 | 8 | - |
<tbody>
</tbody>
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!