Application of IFS, ANDS

Hello @Chaturvedi, Santosh

Just to confirm my understanding of the logic:

• The conditions shown on the right side define which calculation case should be applied.
• Each condition corresponds to the formula shown in the middle.
• The values of t, tmin, tref, and n are variable inputs and may change for each calculation.
• The Excel formula (using IFS and AND) should first evaluate the applicable condition based on the current values of t, tmin, and tref, and then calculate f_thick using the corresponding formula.

In other words, the logic is:

• Check the relationship between t, tmin, and tref.
• Identify which case/condition is satisfied.
• Apply the matching equation for that case.
• Return the calculated f_thick value.

Based on my understanding, the objective is to create one dynamic Excel formula that evaluates the applicable condition for the current values of t, tmin, tref, and n, and then automatically applies the corresponding equation to calculate f_thick.

To illustrate for the fomular, I will assume:

• t = x
• tref = y
• tmin = z
• n = r

The objective is to create a single dynamic Excel formula that:

• Evaluates the relationships between x (t), y (tref), and z (tmin).
• Determines which condition is TRUE.
• Selects the corresponding equation through IFS() and AND() logic.
• Calculates and returns the resulting f_thick value automatically.

The corresponding Excel implementation would be:

=IFS(

AND(z<y,x<=z,z<=10),(10/z)^(r/2)*(y/10)^r,

AND(z<y,x<=y,y<=10),(10/x)^(r/2)*(y/10)^r,

AND(z<y,x>=10,x<=y),(y/x)^r,

AND(z=y,x<=y),1,

x>y,(y/x)^r

)

Or, if actual cell references are used (for example: t=A1, tref=A2, tmin=A3, n=A4):

=IFS(

AND(A3<A2,A1<=A3,A3<=10),(10/A3)^(A4/2)*(A2/10)^A4,

AND(A3<A2,A1<=A2,A2<=10),(10/A1)^(A4/2)*(A2/10)^A4,

AND(A3<A2,A1>=10,A1<=A2),(A2/A1)^A4,

AND(A3=A2,A1<=A2),1,

A1>A2,(A2/A1)^A4

)

*Note:

When implementing the formula, please verify that the listed conditions are both:

• Mutually exclusive (only one condition can be TRUE at a time), and
• Collectively exhaustive (all valid combinations of t, tref, and tmin are covered).

Otherwise, additional conditions or boundary handling may be required to ensure that every valid input produces a result.

*Example 1: Overlapping Conditions (Not Mutually Exclusive)

• Condition A: t <= 10
• Condition B: t <= 20
• Input: t = 8

Result:

• Condition A = TRUE
• Condition B = TRUE

-> Since more than one condition is TRUE, Excel IFS() will return the first match only, which may lead to unintended results.

*Example 2: Missing Boundary Condition (Not Collectively Exhaustive)

• Condition A: t < 10
• Condition B: t > 10
• Input: t = 10

Result:

• Condition A = FALSE
• Condition B = FALSE

-> Since no condition is TRUE, IFS() may return #N/A.

*Example 3: Potential Gap in the Current Logic:

• t = 9
• tref = 25
• tmin = 4

Result:

• tmin < tref -> TRUE
• t ≤ tmin ≤ 10 -> FALSE
• t ≤ tref ≤ 10 -> FALSE
• 10 ≤ t ≤ tref-> FALSE
• tmin = tref -> FALSE
• t > tref -> FALSE

-> Since none of the defined cases are satisfied, the formula would not return a valid result.

This suggests that an additional condition, boundary rule, or clarification of the inequalities may be required to ensure every valid input combination produces a valid f_thick value

Kind regards,

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.