Excel: how to reference a dynamic range through a function? 1

[Mightyginger]

Hi,

I am using an interpolation in Excel and as part of this it takes a range of X inputs and range of Y inputs. Now lets assume the function looks like this:
=interpolate(B1:B8,C1:C8,D2) where the B column are my X values and C are my Y values and D2 is the X value I want to interpolate for. The issue is the interpolation function won't accept empty cells so can I use a CountIf function to figure how many cells are not empty and then adjust the range size dynamically? So have B1:B7 instead? I was thinking I might be able to use a combination of Offset and Match using the number returned from the CountIf. Any thoughts?

Thanks in advance,

Neil.

 

[SkipVought]

Hi,

Check out the OFFSET function with MATCH & COUNTIF.

Here's an example...

faq68-1331

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mightyginger]

Sorry for the delay. Worked a treat. Thanks!!!

 

[SkipVought]

Wunderful! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mightyginger]

Thanks again. Do you think you might be able to help with my post "Possible to append two ranges into one?" in the same forum?