Counting empty cells between two filled cells

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I'm trying to create dynamic ranges using the offset function. The number of columns in the range needs to be the first 'filled' cell to the next 'filled' cell less one.

Example:

| Low | | | | | | | medium| | | | | | | | | | | |high| | | | | | |


So my dynamic range for Low would be:

=offset(A1,0,0,1, 8-1 )

But how do I count that value of 8, starting at 'Low' across to 'medium'?

I'm sure I've seen this somewhere before but i cant find it.

Any help is much appreciated,
thanks, Dan
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
you could use =MATCH("Low",[row reference],0) to find which column contains that header
 
Upvote 0
Can you explain please?

I dont think this returns the number of empty cells between two filled cells in a range?

if A1 is filled and F1 is filled but the range is 20 cells long (A1:T1) i want the function to tell me 4, not 18

Thanks, Dan
 
Upvote 0
Sorry, I thought you were trying to find the column containing the header "Low" and compare this to the column containing ""Medium". You use the MATCH formula to identify where in a list a value appears.

If you wanted to find the item in a list of values which is the min value you could combine MATCH and MIN when looking at the list of numbers, and maybe use MEDIAN to find the middle value. But I may be misinterpreting your requirement
 
Upvote 0
Think I've found something i can use which is based on your answer:-

MATCH("zzzz",A1:Z1)

so long as the range doesn't have three non-blanks in it I'm ok, so need to limit this range size, but that should be ok.


Thanks for putting me on the right track Master of Bait!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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