Finding z-value from L(z)

[emilyanne]

I need to find the z-value but only have the L(z). I know how to go from z-value to L(z) in Excel, using formula: =NORM.S.DIST(z,FALSE)-z*(1-NORM.S.DIST(z,TRUE)), but I do not know a formula to go from L(z) to z-value.

Any help please?

 

[BobUmlas]

Goal seek could come to the rescue

 

[emilyanne]

Thanks, but I am looking for a formula so that it can be automatically calculated for a table of multiple L(z) values.

 

[emilyanne]

Thanks, but I am looking for a formula so that it can be automatically calculated for a table of multiple L(z) values.​

 

[shg]

Do you particularly need to do this in Excel? There's a chart at http://www.ime.unicamp.br/~moretti/ms715/1S_2012/Z-Chart & Loss Function v05.pdf

 

[shg]

Or, using Bob's suggestion and starting with random loss values ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

L(z)​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.63​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1.90​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2.26​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1.51​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2.41​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1.71​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

2.90​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

2.58​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

0.52​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1.61​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1.58​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

0.26​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1.24​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

0.57​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

2.65​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

0.81​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

2.22​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

1.64​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

0.75​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

1.92​

[/td][/tr]
[/table]

... run this ...

Sub LossToZ()
  Dim cell As Range
  
  Application.MaxChange = 0.0001 ' change as desired
  
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1).Value = 0
    .Cells(0, 2).Value = "z"
    
    With .Offset(, 2)
      .FormulaR1C1 = "=NORM.S.DIST(RC[-1], FALSE) - RC[-1] * (1 - NORM.S.DIST(RC[-1], TRUE))"
      For Each cell In .Cells
        cell.GoalSeek Goal:=cell.Offset(, -2).Value2, ChangingCell:=cell.Offset(, -1)
      Next cell
      .EntireColumn.Delete
    End With
  End With
End Sub

... to see

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

L(z)​

[/td][td="bgcolor:#F3F3F3"]

z​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.63​

[/td][td]

-0.40​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1.90​

[/td][td]

-1.89​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2.26​

[/td][td]

-2.26​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1.51​

[/td][td]

-1.48​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2.41​

[/td][td]

-2.41​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1.71​

[/td][td]

-1.69​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

2.90​

[/td][td]

-2.90​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

2.58​

[/td][td]

-2.58​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

0.52​

[/td][td]

-0.22​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1.61​

[/td][td]

-1.59​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1.58​

[/td][td]

-1.55​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

0.26​

[/td][td]

0.32​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1.24​

[/td][td]

-1.18​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

0.57​

[/td][td]

-0.31​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

2.65​

[/td][td]

-2.65​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

0.81​

[/td][td]

-0.66​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

2.22​

[/td][td]

-2.22​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

1.64​

[/td][td]

-1.62​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

0.75​

[/td][td]

-0.57​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

1.92​

[/td][td]

-1.91​

[/td][/tr]
[/table]

 

[emilyanne]

Yes I need to use Excel because the chart only goes to the thousandth. For example, I need to find the exact z-value for the L(z) of 0.00848528, which is not on the chart.

 

[emilyanne]

Oh wow, thank you. I wasn't thinking of using VBA but it looks like it provides exactly what I need.

 

[shg]

You're welcome.

I need to find the exact z-value

"Exact" is pushing it, but you'll need to change the 0.001 to something much smaller, like 0.000000000000001