how to have a sliding formula

It sounds like you will need to go the VB route. In that case, consider the following notion. Assume your timess are in row 1, values in row 2 and function in row 3.

You want a routine that, every time an entry is made in row 2, then f(2) is put in row 3. (the times can be handled seperately)

A worksheet change routine like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If 1 < Target.Cells.Count Then Exit Sub
    If 2 <> Target.Row Then Exit Sub
    Target.Offset(1, 0).Value = myFunction(Target.Value)
End Sub

matched with a (private?) function should meet your needs. Since you already have a worksheet formula for the myFunction value, writing myFunction will be easy.

yes mikerickson, sounds like what I am after. The point is I never used VB.
Could you please explain further by mean of following example: Say row 1 is for hours 00, 01, 02, ... 22, 23, 00, 01, ... Inputs to appear every hour would be multiples of 5 starting from 0 (0, 5, 10, 15, 20, 25, ...). Assume also my function would be a simple multiplication by 10 so that row 3 = 10 * (row 2). Every time an entry is made in cell R(2)C(n), formula is applied only on cell R(3)C(n) to return 10 * R(2)C(n).

I do not know what a private function is.
Should VB code lay in cells of row 3? in cell R(3)C(n)?


yes Wheres_the_Red, this is copy/paste special but only applied to lastest entry. Here is the trick.

I've adapted Mike's original code: try the attached.



James



FormulaToValue.zip

It sounds like
If you enter 5 in B3, you don't want the formula "=10*B3" in C3, but rather you want the value 50 in C3. My notion is that the formula never is in a cell, but the value is.

I assume that Wheres_the_Red adapted that priciple. In your example I would use

Function myFunction (inValue as Double) as Double
  myFunction = 10 * inValue
End Function

I think we're pretty close to the solution.
Yes, at time n only the value in R(3)C(n) should be computed since all previous values in R(3)C(p) with p < n already did and shouldn't be computed again and again.
Yes, if f is my function we have value in R(3)C(n) = f[value in R(2)C(n)] for all n, n standing for hours. Calculation are done for latest n only. f slides along the row 3.

Still wondering: how to apply your VB code only to the latest entry?

The routine I wrote was a Worksheet_Change event and it used the variable Target. Target refers only to the cell being changed. So since the routine acts on Target, it acts only on the cell being changed. (There is code to have the routine do nothing if Target is not in row 3.)