Simplify the code

Hi BishwaR 

OK well let's start with IF.

IF tests a condition and then gives a result if that condition is true, and a different result if that condition is false.

Syntax: IF(Condition, Result_If_True, Result_If_False)

Example: IF(1>0, "Positive", "Negative")

The result is "Positive"

Example: IF(-1>0, "Positive", "Negative")

The result is "Negative"

Now let's look at AND. The AND function tests if all arguments are TRUE and returns TRUE if they are. Otherwise it returns FALSE.

Syntax: AND(Argument_1, Argument_2)

Example: AND(1>0, 2>0)

Result: TRUE

Example: AND(-1>0, 2>0)

Result: FALSE

Example: AND(-1>0, -2>0)

Result: FALSE

Example: AND(1>0, -2>0)

Result: FALSE

NOTE: The AND function only accepts 2 arguments. If you need to test 3 or more arguments/expressions then use && for example

1>0 && 2>0 && 3>0

Result: TRUE

So getting back to using IF with AND, I'll use the examples from above. Remember that the syntax for IF is IF(Condition, Result_If_True, Result_If_False)

Example: IF(AND(1>0, 2>0), "Positive", "Negative")

Result : "Positive" because AND(1>0, 2>0) returns TRUE. Therefore IF returns the Result_If_True

Example: IF(AND(-1>0, 2>0), "Positive", "Negative")

Result : "Negative" because AND(-1>0, 2>0) returns FALSE. Therefore IF returns the Result_If_False

Regards

Phil

Hi BishwaR ,

The first AND() in your formula is used to wrap the following parallel conditions, and has nothing to do with IF() function.

Days Remaining = CALCULATE (IF (AND ( ISNUMBER ( SUM ( COST AMOUNT)),

                                                             AND ( SUM(COST AMOUNT) <>0,

                                                             AND SUM( INVENTORY) <>0 )

                                                            ), 365/ ( ( SUM (COST AMOUT) *4)/ SUM(INVENTORY) ), " "),
                                                            FILTER ( DATE = TODAY())

The problem should be the third AND() in your formula. There are no parentheses after it and it seems redundant. Please check if your formula is correct.

Best Regards,

Jay