0

I want to make a graph in excel. So in one cell, i type the formula. (Let's say Sin(x). Then Somewhere else, i made a table of x values and y values. The x values goes from -5 to 5 with a step size of 0,01. I used substitute in the y column to replace "x" by the value of x next to it. (let's says it become Sin(5). Now i want excel to calculate sin(5). I tried concatenate it with "=" but it's counted as string. I tried the indirect fonction, it didn't work, it gave a ref#, i tried to see the calculation step and it failed on the first step. I tried the value fonction, it didn't work, got a #value. I know that if i carefully copy and paste with values only, then press F2 and hit enter, its gonna calculate. But i don't want to do it manually.

Thats made with the x+2 fonction, whats on the left does itself automatically but it's seen as a string of text by excel, not a function:

enter image description here

Same thing but with sin(x) +1:

enter image description here

A simplified version with two examples. What's written in each cell is written below in between quotation mark.""

enter image description here

It's to give an idea of what i'm trying to do. There's the y column made a substitution of F6. I'd want another column, or this column to show the number value rather then text (this one is kinda useless, the first 3 are better to explain my point).

enter image description here

That's just to see the commands (this one is kinda useless, the first 3 are better to explain my point).

enter image description here

PeterT
  • 8,232
  • 1
  • 17
  • 38
Self learning student
  • 31
  • 4
  • Welcome to [so]! I'm having trouble following what you're trying to do (and *why*). Perhaps if you gave a single start-to-finish example, it would make more sense Show what you have, what you want to have when finished, what you've tried, and why it's not working. See [mcve]. Also screenshots are easier to understand if they are cropped to only include an example, not the entire screen. – ashleedawg Jun 29 '19 at 22:46
  • I have a command(A mathematical fonction) made with the substitute fonction. This command counts as Text (-5+2), i want excel to view it as if i clicked on the cells and wrote (=-5+2) then press enter (it would calculate -5+2 and the cell value would be -3). In one cell i wrote the fonction (x+2) then i want to substitute x by its value in the table. So in the y colum i have =SUBSTITUTE($F$7,"x",$J6) which is -5+2 then below it i have =SUBSTITUTE($F$7,"x",$J7) which is -4.99 +2 ... The endgoal is to write a fonction like sin(x) + x -log(x) in ONE cell and for other cell to use this fonction. – Self learning student Jun 29 '19 at 23:01
  • but they substitute x by the numerical value of x(in the cell to its left) and then to actually calculate the result/numerical value. So i can make a software a bit like desmos but in excel. The only thing i need. THE IMPORTANT PART is: How to transform "-5+2" in a cell from a STRING OF TEXT to an EXCEL FONCTION. This would work with other excel fonction, not only mathematical. – Self learning student Jun 29 '19 at 23:15
  • -5+2 is an exemple. This would allow to write any excel fonction in one place, except it would replace the cell reference by "x". And then a table calling this fonction would replace the "x" by the corresponding cell. – Self learning student Jun 29 '19 at 23:20
  • You can use vba to translate a string to a formula. But maybe there's a better way to solve your real problem. – Ron Rosenfeld Jun 30 '19 at 00:00
  • It's ok if the solution works with VBA Scripts. It doesn't have to work on excel alone. Maybe something that copy the String Value in another case and then update it would work. I don't care about having helper cell too. So how would i do it? I only know a handfull of commands in VBA. – Self learning student Jun 30 '19 at 00:02

0 Answers0