nc_waggoner
New Member
- Joined
- Sep 2, 2016
- Messages
- 21
Hello, I have a reference sheet linked to a Sharepoint file similar to the format below. The blanks do contain the reference formula which is causing issues. I need to figure out how to get the percentage of data points above 1.1 based on the last 10 days with values. So for example, 11/15/17 for column 1 would be 60% since it would cover 11/5 to 11/5 (this does not include 11/8 due to being blank). 11/16 would cover the same range since it too is blank. The idea is to get a daily tracker to see if the percentage above 1.1 is improving or getting worse. How would I set up the formula to count anything above 1.1 based on the last 10 cells with data/numbers (not blanks or formulas). Thank you!
[TABLE="width: 440"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/2/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/3/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]11/4/2017[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]11/5/2017[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/6/2017[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/7/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/8/2017[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/9/2017[/TD]
[TD]2000[/TD]
[TD]850[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/10/2017[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/11/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]11/12/2017[/TD]
[TD]1.1[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]11/13/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/14/2017[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/15/2017[/TD]
[TD]1200[/TD]
[TD]1.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/16/2017[/TD]
[TD][/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 440"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/2/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/3/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]11/4/2017[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]11/5/2017[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/6/2017[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/7/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/8/2017[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/9/2017[/TD]
[TD]2000[/TD]
[TD]850[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/10/2017[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/11/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]11/12/2017[/TD]
[TD]1.1[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]11/13/2017[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]11/14/2017[/TD]
[TD]100[/TD]
[TD]1.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/15/2017[/TD]
[TD]1200[/TD]
[TD]1.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11/16/2017[/TD]
[TD][/TD]
[TD]1.1[/TD]
[TD]1.1[/TD]
[/TR]
</tbody>[/TABLE]