Thread: [SOLVED] Function tagging values with if statements

I wrote a function to change a value in a cell to an ordinal number.

Code:

Function pvalueconversion(pval As Double)
     
    
   
    If (pval < -0.05) Then pvalueconversion = "-1"
    If (-0.05 < pval < -0.01) Then pvalueconversion = "-2"
    If (-0.01 < pval < -0.003) Then pvalueconversion = "-3"
    If (-0.003 < pval < -0.0003) Then pvalueconversion = "-4"
    If (-0.0003 < pval < 0) Then pvalueconversion = "-5"
    
   
    If (pval > 0.05) Then pvalueconversion = "1"
    If (0.01 < pval < 0.05) Then pvalueconversion = "2"
    If (0.003 < pval < 0.01) Then pvalueconversion = "3"
    If (0.0003 < pval < 0.003) Then pvalueconversion = "4"
    If (0 < pval < 0.0003) Then pvalueconversion = "5"
   

End Function

--- All postive values that get passed through the function return a value of "5." If I remove the postive value statements, I get the desired result for negative numbers. When I remove the last for lines, I get correct return of "1" for postiive values that satisfy the statement, however, if I remove the last three lines, I get a value of 2 for everything.

Is this function over defined? I don't understand why I can simply make nested if statements using If Else. I using Else and only got errors.

You are running every function. Just run it until it finds a match like so, maybe:

Code:

Option Explicit

Function pvalueconversion(pVal As Double)

    If (pVal < -0.05) Then
        pvalueconversion = "-1"
    ElseIf (-0.05 < pVal < -0.01) Then
        pvalueconversion = "-2"
    ElseIf (-0.01 < pVal < -0.003) Then
        pvalueconversion = "-3"
    ElseIf (-0.003 < pVal < -0.0003) Then
        pvalueconversion = "-4"
    ElseIf (-0.0003 < pVal < 0) Then
        pvalueconversion = "-5"
    ElseIf (pVal > 0.05) Then
        pvalueconversion = "1"
    ElseIf (0.01 < pVal < 0.05) Then
        pvalueconversion = "2"
    ElseIf (0.003 < pVal < 0.01) Then
        pvalueconversion = "3"
    ElseIf (0.0003 < pVal < 0.003) Then
        pvalueconversion = "4"
    ElseIf (0 < pVal < 0.0003) Then
        pvalueconversion = "5"
    Else
        pvalueconversion = "0"
    End If
    
End Function

I'm having trouble with your ranges. Can you just make a table showing the ranges and the result so it's crystal clear?

Maybe this to order it and reduce required calcs:

Code:

Option Explicit

Function pvalueconversion(pVal As Double)
    If pVal > 0.05 Then
        pvalueconversion = "1"
    ElseIf pVal > 0.01 Then
        pvalueconversion = "2"
    ElseIf pVal > 0.003 Then
        pvalueconversion = "3"
    ElseIf pVal > 0.0003 Then
        pvalueconversion = "4"
    ElseIf pVal > 0 Then
        pvalueconversion = "5"
    ElseIf pVal > -0.0003 Then
        pvalueconversion = "-5"
    ElseIf pVal > -0.003 Then
        pvalueconversion = "-4"
    ElseIf pVal > -0.01 Then
        pvalueconversion = "-3"
    ElseIf pVal > -0.05 Then
        pvalueconversion = "-2"
    Else
        pvalueconversion = "-1"
    End If
    
End Function