 |
Microsoft Dynamics Support
Visit the Microsoft Dynamics newsgroups and exchange ideas with a community of business professionals and product experts from around the globe.
|
|
|
| Author |
Message |
jcriswel@gmail.com
Guest
|
 Posted: Thu May 15, 2008 6:14 pm Post subject: NESTED IF/THEN/ELSE |
 |
|
Is it possible to do nested if then else? e.g. if A<0 if A<0 and B<0
then A*B else A/B else A*C Sort of like the IIF function in VBA |
|
| Back to top |
|
 |
|
|
jcriswel@gmail.com
Guest
|
| Posted: Thu May 15, 2008 10:49 pm Post subject: Re: NESTED IF/THEN/ELSE |
|
|
On May 15, 3:01 pm, Rick Tenorio [MSFT]
<RickTenorioM...@discussions.microsoft.com> wrote:
| Quote: |
No. Nested If/Then/Else formulas are not supported.
There may be a way to get the same affect by using multiple If/Then/Else
formulas in multiple rows or columns.
--
Rick Tenorio MCDST, MCP [MSFT]
Microsoft Corporation
**This posting is provided "AS IS" with no warranties, and confers no
rights.**
"jcris...@gmail.com" wrote:
Is it possible to do nested if then else? e.g. if A<0 if A<0 and B<0
then A*B else A/B else A*C Sort of like the IIF function in VBA- Hide quoted text -
- Show quoted text -
|
Rick,
Thank you for your response. This issue is related to my earlier
question about "IF ABS". Actually you should look at this link:
http://www.microsoft.com/learning/en/us/syllabi/8400bfinal.mspx
Microsoft Course 8400B advertizes that there is a function called "IF
ABS". I have found as you that this is not true - an absolute
function does not exist.
You suggest in my other post that I look at the XRC function in the
column layout. That works most of the time but not all of the time.
It will not work if the row format Normal Balance is not a C and the
denominator is a negative and the numerator is also a negative. As we
both know from high school math, a negative divided by a negative will
yield a positive. This is not going to work in a "B/<W> %"
calculation.
We did solve the problem...
This is what we did:
We created a non-printing column that created a surrogate absolute
value. This is what it looks like:
IF L<0 THEN L*-1 ELSE L
Then we used this surrogate column as the denominator in our "B/<W> %"
calculation, e.g.:
N/(Surrogate Absolute Value) = Correct Percent Calculation
This is a work around for the absence of an ABS function. As it turns
out, an ABS function is essential to handle every case of fractional
calculations. To my point, you have the following possible
conditions:
1. C Normal Balance; numerator positive - denominator positive
2. C Normal Balance; numerator negative - denominator positive
3. C Normal Balance; numerator positive - denominator negative
4. C Normal Balance; numerator negative - denominator negative
5. Null Normal Balance; numerator positive - denominator positive
6. Null Normal Balance; numerator negative - denominator positive
7. Null Normal Balance; numerator positive - denominator negative
8. Null Normal Balance; numerator negative - denominator negative
XCR will work in most cases, but the case where it does not work is
case number eight. We cannot have a solution that will not work in
all eight cases. Therefore, we chose the "surrogate absolute value"
solution.
We think that Microsoft should have focused more on an ABS function
with its service packs than an XCR solution because the XCR solution
has some exceptions. The ABS solution has none!
John Criswell |
|
| Back to top |
|
|
|
Rick Tenorio [MSFT]
Guest
|
| Posted: Fri May 16, 2008 1:01 am Post subject: RE: NESTED IF/THEN/ELSE |
|
|
No. Nested If/Then/Else formulas are not supported.
There may be a way to get the same affect by using multiple If/Then/Else
formulas in multiple rows or columns.
--
Rick Tenorio MCDST, MCP [MSFT]
Microsoft Corporation
**This posting is provided "AS IS" with no warranties, and confers no
rights.**
"jcriswel@gmail.com" wrote:
| Quote: |
Is it possible to do nested if then else? e.g. if A<0 if A<0 and B<0
then A*B else A/B else A*C Sort of like the IIF function in VBA
|
|
|
| Back to top |
|
|
|
|
Rick Tenorio [MSFT]
Guest
|
| Posted: Fri May 16, 2008 4:55 am Post subject: Re: NESTED IF/THEN/ELSE |
|
|
I apologize. There IS a function of IF ABS that can be used in combination
with the XCR that I was not aware of. The example they use is
IF ABS (C-B)>10000 THEN C-B
But you cannot use it in a nested IF/Then/Else. The way you discovered
(using non-printing columns to determine the value) is the only workaround
that I know of.
If you have access to the Microsoft FRx knowledgebase, you can search for
the KB article titled "Variance Column Produces a Negative Number When It
Should Be Positive.
--
Sorry for the confusion.
Rick Tenorio MCDST, MCP [MSFT]
Microsoft Corporation
**This posting is provided "AS IS" with no warranties, and confers no
rights.**
"jcriswel@gmail.com" wrote:
| Quote: |
On May 15, 3:01 pm, Rick Tenorio [MSFT]
RickTenorioM...@discussions.microsoft.com> wrote:
No. Nested If/Then/Else formulas are not supported.
There may be a way to get the same affect by using multiple If/Then/Else
formulas in multiple rows or columns.
--
Rick Tenorio MCDST, MCP [MSFT]
Microsoft Corporation
**This posting is provided "AS IS" with no warranties, and confers no
rights.**
"jcris...@gmail.com" wrote:
Is it possible to do nested if then else? e.g. if A<0 if A<0 and B<0
then A*B else A/B else A*C Sort of like the IIF function in VBA- Hide quoted text -
- Show quoted text -
Rick,
Thank you for your response. This issue is related to my earlier
question about "IF ABS". Actually you should look at this link:
http://www.microsoft.com/learning/en/us/syllabi/8400bfinal.mspx
Microsoft Course 8400B advertizes that there is a function called "IF
ABS". I have found as you that this is not true - an absolute
function does not exist.
You suggest in my other post that I look at the XRC function in the
column layout. That works most of the time but not all of the time.
It will not work if the row format Normal Balance is not a C and the
denominator is a negative and the numerator is also a negative. As we
both know from high school math, a negative divided by a negative will
yield a positive. This is not going to work in a "B/<W> %"
calculation.
We did solve the problem...
This is what we did:
We created a non-printing column that created a surrogate absolute
value. This is what it looks like:
IF L<0 THEN L*-1 ELSE L
Then we used this surrogate column as the denominator in our "B/<W> %"
calculation, e.g.:
N/(Surrogate Absolute Value) = Correct Percent Calculation
This is a work around for the absence of an ABS function. As it turns
out, an ABS function is essential to handle every case of fractional
calculations. To my point, you have the following possible
conditions:
1. C Normal Balance; numerator positive - denominator positive
2. C Normal Balance; numerator negative - denominator positive
3. C Normal Balance; numerator positive - denominator negative
4. C Normal Balance; numerator negative - denominator negative
5. Null Normal Balance; numerator positive - denominator positive
6. Null Normal Balance; numerator negative - denominator positive
7. Null Normal Balance; numerator positive - denominator negative
8. Null Normal Balance; numerator negative - denominator negative
XCR will work in most cases, but the case where it does not work is
case number eight. We cannot have a solution that will not work in
all eight cases. Therefore, we chose the "surrogate absolute value"
solution.
We think that Microsoft should have focused more on an ABS function
with its service packs than an XCR solution because the XCR solution
has some exceptions. The ABS solution has none!
John Criswell
|
|
|
| Back to top |
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
Profile 
Log in to check your private messages | 
FAQ 
Search 
Memberlist 
Usergroups 
Log in
Register
