Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to make the gridlines of a chart square
I would like to have a chart in excel with the x-axis and y-axis ranges from
-5 to 5 as illustrated in the link below. http://phpaw.com/myscrpit/milt-up/jp...1126029383.jpg The problem is that I don't get square gridlines. I can only do this visually by resizing the chart. I saw Jon Peltier traffic example of making the gridlines square (http://peltiertech.com/Excel/Charts/SquareGrid.html) but I found that when I run it the scale changes to a value greater than 5. Also if I try to add titles the gridlines become not squared. Is there a way to fix the range of axes and have square gridlines? The other problem I have which you may be able to help me with is that I am trying to write a function to find the partial derivative of a function. The function can then be called from a worksheet cell almost exactly as one would write them normally. For example, we have this equation f(Z1, Z2, Z3) = Z1^2+Z2*Z3^3-Z3^0.5 and Z1 = 2, Z2 = 4, Z3 = 6 if we want to find the partial derivative with respect to Z1 then: = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z1") = 4 with respect to Z2 = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 4,"Z2") = 216 and so on ............. So all I am doing is to substitute the values of Z2 and Z3 in the function and then do a normal differentiation with respect to Z1. Similarity, I substitute Z1 and Z3 to get the derivative with respect to Z2, ..... I don't know what is wrong with the code. So can anybody help me to correct it and get it working. Thanks for any help __________________________________________________ __ Option Explicit Dim Z1 As Double Dim Z2 As Double Dim Z3 As Double Function DerivativeZ(func As String, a As Double, V As String) As Double Const h = 0.0001 Dim n1 As Double, n2 As Double Z1 = Range("C13") Z2 = Range("C14") Z3 = Range("C15") Select Case UCase(Left(V, 2)) Case Is = "Z1" func = Replace(func, "Z2", Z2) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z1) - eval(func, a - (h / 2), Z1)) / h n2 = (eval(func, a + h, Z1) - eval(func, a - h, Z1)) / (2 * h) Case Is = "Z2" func = Replace(func, "Z1", Z1) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z2) - eval(func, a - (h / 2), Z2)) / h n2 = (eval(func, a + h, Z2) - eval(func, a - h, Z2)) / (2 * h) Case Is = "Z3" func = Replace(func, "Z1", Z1) func = Replace(func, "Z2", Z2) n1 = (eval(func, a + (h / 2), Z3) - eval(func, a - (h / 2), Z3)) / h n2 = (eval(func, a + h, Z3) - eval(func, a - h, Z3)) / (2 * h) End Select DerivativeZ = (4 * n1 - n2) / 3 End Function Function eval(funct As String, Z As Double, V As String) As Double eval = Evaluate(funct) Select Case UCase(Left(V, 2)) Case Is = "Z1" Z1 = Z Case Is = "Z2" Z2 = Z Case Is = "Z3" Z3 = Z End Select End Function |
#2
|
|||
|
|||
Sorry,
I have misspelled terrific "Jon Peltier terrific example" |
#3
|
|||
|
|||
I find Jon Peltier utility fasinating.
I have done three pdf files showing before and after effect, but am not aware how to attach them to show you. In words, when I do first time transformation (to make grid square), I cannot predict major tick setting. I may need to adjust the major tick setting and transform the grid again. Would it be nice if we can control the maximum values of the axes. "Ali Baba" wrote: I would like to have a chart in excel with the x-axis and y-axis ranges from -5 to 5 as illustrated in the link below. http://phpaw.com/myscrpit/milt-up/jp...1126029383.jpg The problem is that I don't get square gridlines. I can only do this visually by resizing the chart. I saw Jon Peltier traffic example of making the gridlines square (http://peltiertech.com/Excel/Charts/SquareGrid.html) but I found that when I run it the scale changes to a value greater than 5. Also if I try to add titles the gridlines become not squared. Is there a way to fix the range of axes and have square gridlines? The other problem I have which you may be able to help me with is that I am trying to write a function to find the partial derivative of a function. The function can then be called from a worksheet cell almost exactly as one would write them normally. For example, we have this equation f(Z1, Z2, Z3) = Z1^2+Z2*Z3^3-Z3^0.5 and Z1 = 2, Z2 = 4, Z3 = 6 if we want to find the partial derivative with respect to Z1 then: = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z1") = 4 with respect to Z2 = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 4,"Z2") = 216 and so on ............. So all I am doing is to substitute the values of Z2 and Z3 in the function and then do a normal differentiation with respect to Z1. Similarity, I substitute Z1 and Z3 to get the derivative with respect to Z2, ..... I don't know what is wrong with the code. So can anybody help me to correct it and get it working. Thanks for any help __________________________________________________ __ Option Explicit Dim Z1 As Double Dim Z2 As Double Dim Z3 As Double Function DerivativeZ(func As String, a As Double, V As String) As Double Const h = 0.0001 Dim n1 As Double, n2 As Double Z1 = Range("C13") Z2 = Range("C14") Z3 = Range("C15") Select Case UCase(Left(V, 2)) Case Is = "Z1" func = Replace(func, "Z2", Z2) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z1) - eval(func, a - (h / 2), Z1)) / h n2 = (eval(func, a + h, Z1) - eval(func, a - h, Z1)) / (2 * h) Case Is = "Z2" func = Replace(func, "Z1", Z1) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z2) - eval(func, a - (h / 2), Z2)) / h n2 = (eval(func, a + h, Z2) - eval(func, a - h, Z2)) / (2 * h) Case Is = "Z3" func = Replace(func, "Z1", Z1) func = Replace(func, "Z2", Z2) n1 = (eval(func, a + (h / 2), Z3) - eval(func, a - (h / 2), Z3)) / h n2 = (eval(func, a + h, Z3) - eval(func, a - h, Z3)) / (2 * h) End Select DerivativeZ = (4 * n1 - n2) / 3 End Function Function eval(funct As String, Z As Double, V As String) As Double eval = Evaluate(funct) Select Case UCase(Left(V, 2)) Case Is = "Z1" Z1 = Z Case Is = "Z2" Z2 = Z Case Is = "Z3" Z3 = Z End Select End Function |
#4
|
|||
|
|||
There are a few versions of that procedure floating around on my hard
disk, and I suspect what I'd posted is rather antique. The thing is, the procedure has to be run after everything else is done (labels, etc.), and you may need to tweak the scales and rerun it. Unfortunately, working with Excel charts, the best programmer in the world (who is way better than I am, believe me) can't do better than "pretty good". - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ PY & Associates wrote: I find Jon Peltier utility fasinating. I have done three pdf files showing before and after effect, but am not aware how to attach them to show you. In words, when I do first time transformation (to make grid square), I cannot predict major tick setting. I may need to adjust the major tick setting and transform the grid again. Would it be nice if we can control the maximum values of the axes. "Ali Baba" wrote: I would like to have a chart in excel with the x-axis and y-axis ranges from -5 to 5 as illustrated in the link below. http://phpaw.com/myscrpit/milt-up/jp...1126029383.jpg The problem is that I don't get square gridlines. I can only do this visually by resizing the chart. I saw Jon Peltier traffic example of making the gridlines square (http://peltiertech.com/Excel/Charts/SquareGrid.html) but I found that when I run it the scale changes to a value greater than 5. Also if I try to add titles the gridlines become not squared. Is there a way to fix the range of axes and have square gridlines? The other problem I have which you may be able to help me with is that I am trying to write a function to find the partial derivative of a function. The function can then be called from a worksheet cell almost exactly as one would write them normally. For example, we have this equation f(Z1, Z2, Z3) = Z1^2+Z2*Z3^3-Z3^0.5 and Z1 = 2, Z2 = 4, Z3 = 6 if we want to find the partial derivative with respect to Z1 then: = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z1") = 4 with respect to Z2 = DerivativeZ("Z1^2+Z2*Z3^3-Z3^0.5", 4,"Z2") = 216 and so on ............. So all I am doing is to substitute the values of Z2 and Z3 in the function and then do a normal differentiation with respect to Z1. Similarity, I substitute Z1 and Z3 to get the derivative with respect to Z2, ..... I don't know what is wrong with the code. So can anybody help me to correct it and get it working. Thanks for any help ________________________________________________ ____ Option Explicit Dim Z1 As Double Dim Z2 As Double Dim Z3 As Double Function DerivativeZ(func As String, a As Double, V As String) As Double Const h = 0.0001 Dim n1 As Double, n2 As Double Z1 = Range("C13") Z2 = Range("C14") Z3 = Range("C15") Select Case UCase(Left(V, 2)) Case Is = "Z1" func = Replace(func, "Z2", Z2) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z1) - eval(func, a - (h / 2), Z1)) / h n2 = (eval(func, a + h, Z1) - eval(func, a - h, Z1)) / (2 * h) Case Is = "Z2" func = Replace(func, "Z1", Z1) func = Replace(func, "Z3", Z3) n1 = (eval(func, a + (h / 2), Z2) - eval(func, a - (h / 2), Z2)) / h n2 = (eval(func, a + h, Z2) - eval(func, a - h, Z2)) / (2 * h) Case Is = "Z3" func = Replace(func, "Z1", Z1) func = Replace(func, "Z2", Z2) n1 = (eval(func, a + (h / 2), Z3) - eval(func, a - (h / 2), Z3)) / h n2 = (eval(func, a + h, Z3) - eval(func, a - h, Z3)) / (2 * h) End Select DerivativeZ = (4 * n1 - n2) / 3 End Function Function eval(funct As String, Z As Double, V As String) As Double eval = Evaluate(funct) Select Case UCase(Left(V, 2)) Case Is = "Z1" Z1 = Z Case Is = "Z2" Z2 = Z Case Is = "Z3" Z3 = Z End Select End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make gridlines permanent? | Excel Discussion (Misc queries) | |||
pie-charting non-numeric data | Charts and Charting in Excel | |||
Locking gridlines to be square - axes to same scale ? | Charts and Charting in Excel | |||
how do i make one chart that my ees can input info, and have a se. | Excel Worksheet Functions | |||
How do I make the Y axis in an excel chart the category axis? | Charts and Charting in Excel |