Counting empty cells between two filled cells

[PivotIdiot]

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

 

[baitmaster]

you could use =MATCH("Low",[row reference],0) to find which column contains that header

 

[PivotIdiot]

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

 

[baitmaster]

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

 

[PivotIdiot]

I wrote an awesome reply but got timed out

 

[PivotIdiot]

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!