Help with creating excel function

Sujit12

New Member
Joined
Apr 2, 2015
Messages
3
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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)
 
Last edited:
Upvote 0
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.

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


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




End Function
 
Upvote 0
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.
 
Upvote 0
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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