Help with Counting by Dynamic Range & Skip Blanks

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]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top