Fixing my interpolation function.

[vegard1992]

So here's the deal, I work for a company that does "floor lifting". I.e. when a house has sunk we come in and do measurements, and give people an estimate of what we can do, and a recommendation.

One way we collect measurements is by placing different height values at x, y locations in an excel file. To get a quick/rough estimate of how things look, we use an interpolation function. It takes the value of two cells and -- according to the distance of the current cell -- computes an interpolated value. Currently we are just doing a linear interpolation.

Thing is some of our projects involve larger buildings; with a great floor surface area. Copy+paste becomes a chore in this case.

Here is the function in its current state http://prntscr.com/mervhm

The first problem i ran into while dragging, was the original cell references changing.

Like so http://prntscr.com/merxe0 (every time you move a cell to the right, the reference moves a letter to the right)

A quick google showed that i could use absolute references to fix this. However, when i drag now, it just fills in the answer of the cell i've dragged from. I suspect "ActiveCell" is the culprit here, and i imagine it always references the cell that i "drag from".

Like so (code is the same, just using absolute references) http://prntscr.com/mery0j

This is where I defer to the sage advice of the mrexcel.com forums

Thank you for reading.

 

[Rijnsent]

Hi Vegard,
what's your desired outcome? If you could provide some samples (including input values) that would be really helpfull to see where your function/logic derails. As is, I have a hard time grasping what the issue is.
Thanks,
Koen

 

[offthelip]

I think all you need to do is get rid of the Active cell line by commenting it out and adding CELLC as one of the parameters, this will then allow you to define it in the cell where you write the equations and you can choose whether to use absolute addressing or not.

Function INTERP3(CELL1, CELL2, CELLC) As Double

 ' Set CELLC =ActiveCell ' Address(ROW(), Column() as range

 

[vegard1992]

offthelip your solution works great!

I'm just curious, is there any solution where I can specify only CELL1 and CELL2 WITHOUT absolute references, and still have this functionality?
it is more cumbersome having to type the dollar signs after each letter/number, and the extra cell.
or will this be "out of the purview" of the function; the input decided by excel before the function is called, with no way of really knowing what cell references were intended?

thank you

 

[offthelip]

I like the idea of being able to do that myself but I haven't found an easy way where you want some cells references to increment and others to stay the same.
when things have got very difficult with this type of probelm , I do use EXCEL itself to build up the strings in the cells before turning them into equations.
As a simple example:
="Average(A" & ROW() - 1 & "3:B" & ROW()+3 & "10)"
This creates a text string which you can then turn into a function by adding the = sign
Try turning this into a string and then copying it down and you will see what I mean