how to have a sliding formula

[KrispRolls]

Hi,

I feed my Excel file every hour along a row, with one value an hour.
The same formula should be applied only to the latest input. Outputs are displayed in the cell right under the cell containing the corresponding input.
So, basically, what is obtained is a matrix with 3 rows (hours, input and output) and a growing number of columns.

How to remove a formula once an output is computed, to store the output itself and not anymore the formula that allowed to compute this output?
Need a macro? VB? Can I get by with Excel suite of functions only?

Thanks in advance for any insight.

VH

[Wheres_the_Red]

Hi VH

Sounds like you're looking for Copy then Edit, Paste Sepcial, Values.


Depending on how you want to do this, you can:
- use the mouse and menu
- set up a button to do the paste
- set up a macro to run over specific cells, for example cells with a value not zero, and a formula as contents.

Is this what you're looking for?
James

[mikerickson]

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:

Please Login or Register  to view this content.

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.

[KrispRolls]

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.

[Wheres_the_Red]

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



James



FormulaToValue.zip

[mikerickson]

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

Please Login or Register  to view this content.

[KrispRolls]

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?

[mikerickson]

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.)