0

I am creating a worksheet for Simpson's Rule and I would like to make it flexible for different equations.

So I have a cell just for the endpoints, step size and formula, and then a table for my calculations.

I know I can just directly input the formula that I want into the table. But is there a way to do it such that when I change the formula cell, the value will change as well?

For example:

Example

So if I changed my f(x) cell to x, it should give me (0, 1, ..., 10) under f(x) for the table.

Additionally, is there a way to stop autofill the multiplier column? I know that the first and last entries should be 1, and the middle should be the sequence of (4, 2, 4, 2, ..., 4). Is there a way to automate this?

mischva11
  • 2,811
  • 3
  • 18
  • 34
A. Belrose
  • 1
  • 4
  • Welcome to Stack Overflow! I'm afraid that your only way to achieve what you wish to do is by the use of VBA and that's because you need to repeat (loop) the same function several times. Try here: https://www.excel-easy.com/vba/loop.html – Hila DG Oct 30 '18 at 00:49

1 Answers1

0

You can evaluate a string using Excels EVALUATE function. Check out this SO post here.

It is still (sort of) possible to do this without VBA by using a named range. EVALUATE is an old Excel v4 function that its use of will require saving as a macro-enabled workbook, but you won't need any VBA code.

  • Assuming your f(x) formula is entered in cell B5, and your x variables start in cell A8. Then highlight cell B8 and goto Formulas tab and click 'Define Name'.
  • Enter a name (e.g. 'f_x') and in the 'Refers to:' field enter this formula =EVALUATE(SUBSTITUTE($B$5, "x", ADDRESS(ROW($A8),COLUMN($A8))))
  • Then in cell A8, enter the formula =f_x and fill down.

You can fill down as far as you like and use some IF statements or something to hide anything beyond the end point.

hughg
  • 191
  • 9