Help with creating excel function

[Sujit12]

Hey Guys,

I am very new to creating functions using Microsoft VBA and hence need your help for the same.

I want to create a function which should perform the operation mentioned below.

Function IDW(StartValue, EndValue, CurrentPoint)


IDW = (StartValue * (Row(EndValue) - Row(CurrentPoint)) + EndValue * (Row(CurrentPoint) - Row(StartValue)) / (Row(EndValue) - Row(StartValue))

Kindly help how to proceed?

 

[Shrikant]

what is the expected result?

can you please elaborate?

 

[Sujit12]

Hey,

The aim is to create a function for inverse distance weighting interpolation. It is used for gap filling of environmental parameters.

Consider this sequence 3,4, , , ,6 with three missing values between 4 and 6. IDW function's output will be these three values. Here 4 is the start value and 6 is the end value. Current point may be any of the three missing points.

In an excel sheet, if the arrangement is like below:

3
4
_
_
_
6

then interpolated value of first missing point will be =
(4 * (Row position of 6 - Row position of first missing point) + 6 * (Row position of first missing point - Row position of 4) )/ (Row position of 6 - Row position of 4)

 

[Shrikant]

Hey,

The aim is to create a function for inverse distance weighting interpolation. It is used for gap filling of environmental parameters.

Consider this sequence 3,4, , , ,6 with three missing values between 4 and 6. IDW function's output will be these three values. Here 4 is the start value and 6 is the end value. Current point may be any of the three missing points.

I am a little confused about usage of ROW function in your original post. Because ROW will give integer.

Moreover, do you wish to know 3 missing points or just a value based on your formula ?

I have put your calculation in this function as per my understanding of your problem.

Function IDW(StartValue As Long, EndValue As Long, CurrentPoint As Long)


IDW = (StartValue * (EndValue - CurrentPoint) + EndValue * (CurrentPoint - StartValue)) / (EndValue - StartValue)




End Function

 

[Sujit12]

Hey..

Thanks.

Suppose my current point is at B4. I want the output of 'row position of currentpoint/B4' as 4. ROW(B4) function gives the value 4 in excel worksheet. But, I don't know how to employ the same in VBA.

 

[Shrikant]

Function IDW(StartValue As Double, EndValue As Double, StartPosition As Long, EndPosition As Long, PointPosition As Long) As Double


IDW = (StartValue * (EndPosition - PointPosition) + EndValue * (PointPosition - StartPosition)) / (EndPosition - StartPosition)


End Function

as per your example
for first missing Value
=IDW(4,6,2,6,3)

4 is start value, 6 is end value, 2 is position of 4 in range, 6 is position of 6 in range, 3 is position of missing point
result will be 4.5

for Second missing point
=IDW(4,6,2,6,4)
For 3rd missing point
=IDW(4,6,2,6,5)


Of course you can refer 4 and 6 from Cells. Here for explanation purpose i have put direct values.