求助编写包含三个条件的嵌套IF AND函数语句

Let's craft the exact formula you need based on your three conditions. Here's the working version, followed by a breakdown so you understand how each part works:

Final Formula #

=IF(AND(A1="", G1=""), "", IF(AND(A1>0, G1=""), E1-A1, IF(AND(A1>0, G1>0), G1-A1, "")))

Breakdown of Each Condition #

Let's walk through each layer to make it clear:

• First Check: AND(A1="", G1="") – This checks if both A1 and G1 are empty. If true, it returns an empty cell ("").
• Second Check: If the first condition fails, we check AND(A1>0, G1="") – meaning A1 has a positive value and G1 is empty. If true, it calculates E1-A1.
• Third Check: If the first two conditions fail, we check AND(A1>0, G1>0) – both A1 and G1 have positive values. If true, it calculates G1-A1.
• Default Case: The final "" ensures that if none of your specified conditions are met (like if A1 is negative or G1 is negative), the formula returns an empty cell instead of an error or unexpected value.

Optional Simplification #

You can also streamline the formula a bit since the last two conditions both require A1>0 – we can pull that up to reduce repetition:

=IF(AND(A1="", G1=""), "", IF(A1>0, IF(G1="", E1-A1, G1-A1), ""))

This version works exactly the same, but cuts down on one AND function by first checking if A1 is positive, then handling G1's cases.

内容的提问来源于stack exchange，提问作者karen