nestedif function

[Dave F]

I'm creating a UDF which I call nested if.

Given the following table:

P1 P2
11 6
22 3
-22 6
-57.3 4
6 6
-5 7
4 3

I've put the following code:

Function NestedIf(P1, P2)
If P1 > 10 Then
If P2 > 5 Then NestedIf = 1 Else _
NestedIf = 2
ElseIf P1 < -10 Then
If P2 > 5 Then
NestedIf = 3
Else
NestedIf = 4
End If
Else
If P2 > 5 Then
If P1 = P2 Then NestedIf = 5 Else _
NestedIf = 6
Else
NestedIf = 7
End If
End If
End Function

When I run this function I get the following (the function is in the
right-hand column)

P1 P2 NestedIf(P1, P2)
11 6 2
22 3 2
-22 6 3
-57.3 4 4
6 6 5
-5 7 6
4 3 7

As far as I can tell, the NestedIf value for the first row should be 1. The
rest of it looks to work correctly. Any ideas?

 

[Norman Jones]

Hi Dave,

For P1 and P2 values of 11 and 6, your function returns a value of 1 for me,
as expected.

I would verify that the P1 and P2 values are what you believe them to be and
that calculation is set to automatic.

 

[Dave F]

Calculation is set to automatic.

Glad to hear the logic is correct. Now why won't it calculate correctly on
my computer? Maybe closing and opening the workbook will fix it.

Dave

 

[Dave F]

Well, I did something different than what I say below.

I deleted the calculation in the first row of the NestedIf column and copied
the function from another cell and pasted it there.

Suddenly it calculates correctly.

Weird.

 

[Norman Jones]

Hi Dave,

I deleted the calculation in the first row of the NestedIf column and
copied
the function from another cell and pasted it there.

Suddenly it calculates correctly

Weird.

In that case, almost certainly, the P1 and P2 arguments passed to the
function were incorrect.