Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
nestedif function
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
nestedif function
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. --- Regards, Norman "Dave F" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
nestedif function
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 "Norman Jones" wrote: 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. --- Regards, Norman "Dave F" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
nestedif function
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. "Dave F" wrote: 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 "Norman Jones" wrote: 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. --- Regards, Norman "Dave F" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
nestedif function
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. --- Regards, Norman "Dave F" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selectively replace cells based on two ranges of criteria - nestedIF() statements? | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |