Thread: 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

Sorry,
I have misspelled terrific "Jon Peltier terrific example"

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
>

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