Hi all,
I am having problems sorting out a formula that can include all of my criteria.
I need to find the average (column C- RR Interval) only if there is more then 2 values recorded in the past 7 days (Column B) for a participant (Column A- Name).
Example is below:
[TABLE="width: 357"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]RR Interval[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]26/01/2015[/TD]
[TD]1093.7[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]27/01/2015[/TD]
[TD]1276.9[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]28/01/2015[/TD]
[TD]1175.7[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]29/01/2015[/TD]
[TD]1210.1[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]30/01/2015[/TD]
[TD]1230.1[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]31/01/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]1/02/2015[/TD]
[TD]1064.3[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]2/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]3/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]4/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]4/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]5/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]6/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]7/02/2015[/TD]
[TD]1102.4[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]8/02/2015[/TD]
[TD]1147.2[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]9/02/2015[/TD]
[TD]1150.7[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]10/02/2015[/TD]
[TD]1189.1[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]11/02/2015[/TD]
[TD]1137[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]12/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]13/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]14/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]15/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]16/02/2015[/TD]
[TD]1149.2[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]17/02/2015[/TD]
[TD]1187.6[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]18/02/2015[/TD]
[TD]1191[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]19/02/2015[/TD]
[TD]1198.3[/TD]
[/TR]
</tbody>[/TABLE]
What I have so far is AVERAGEIFS(C:C,B:B,"<="&B2,$B:$B,">"&$B2-7,$A:$A,$A2) .... but this averages all values even if there is 2 or less values between the two dates.
Any help would be appreciated.
I am having problems sorting out a formula that can include all of my criteria.
I need to find the average (column C- RR Interval) only if there is more then 2 values recorded in the past 7 days (Column B) for a participant (Column A- Name).
Example is below:
[TABLE="width: 357"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]RR Interval[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]26/01/2015[/TD]
[TD]1093.7[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]27/01/2015[/TD]
[TD]1276.9[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]28/01/2015[/TD]
[TD]1175.7[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]29/01/2015[/TD]
[TD]1210.1[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]30/01/2015[/TD]
[TD]1230.1[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]31/01/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]1/02/2015[/TD]
[TD]1064.3[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]2/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]3/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]4/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]4/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]5/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]6/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]7/02/2015[/TD]
[TD]1102.4[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]8/02/2015[/TD]
[TD]1147.2[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]9/02/2015[/TD]
[TD]1150.7[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]10/02/2015[/TD]
[TD]1189.1[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]11/02/2015[/TD]
[TD]1137[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]12/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]13/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]14/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]15/02/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]16/02/2015[/TD]
[TD]1149.2[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]17/02/2015[/TD]
[TD]1187.6[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]18/02/2015[/TD]
[TD]1191[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD]19/02/2015[/TD]
[TD]1198.3[/TD]
[/TR]
</tbody>[/TABLE]
What I have so far is AVERAGEIFS(C:C,B:B,"<="&B2,$B:$B,">"&$B2-7,$A:$A,$A2) .... but this averages all values even if there is 2 or less values between the two dates.
Any help would be appreciated.