Are you familiar with the basics of the IF statement? One way of
representing this in a programming language is:
IF some_condition
THEN action_if_true
ELSE action_if_false
ENDIF
and the actions could comprise many statements that need to be carried
out if the condition is true or if it is false. Those statements could
themselves involve other (so-called "nested") IF statements, which
will take on the same form, i.e. IF..Then..Else..Endif, and often
these will be shown indented to help them stand out as being a
subsidiary block of statements.
In Excel this is written as one statement, i.e.:
=IF(condition, action_if_true, action_if_false)
so the words THEN, ELSE and ENDIF are not needed. However, you could
still break the formula up into these fundamental building blocks if
tht helps you to visualise what is going on, i.e.:
IF($A1388="z"
,""
,
IF(AW1388=""
,"N/A"
,
IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1
,""
,"CHG"
)
)
)
Here the first comma is equivalent to THEN and the second comma to
ELSE. The close bracket is equivalent to ENDIF. So the first part of
this has a condition of A1388="z" - if this is true then the action is
to return "" (i.e. a blank cell), and that is all that would happen.
If it is not true, however, then we encounter another IF with a
condition of AW1388="". So, if this cell is empty the function would
return "N/A" (and that's all), but if the cell is not empty then we
have yet another IF - this time the condition is looking to see if
there is only one cell in AI4:AI2000 which is the same as AW1388 (you
seem to have had a sheet deleted as you have a #REF error, but I'm
ignoring this for now). If there is only one cell that matches AW1388
then an empty string is returned, otherwise the text CHG will be
returned.
So ultimately there are 4 outcomes of this formula - we might get ""
returned, or "N/A", or "", or "CHG", depending on the various
conditions.
Another way of representing the IF structure is diagramatically using
a diamond-shaped box to ask the question (for the criteria), and this
has only a true or a false answer - you can emerge from the box to the
left if the answer is true (and then carry out some further actions),
or to the right if it is false (and do the false-actions), and then
these two branches come together again. Some people like to visualise
these things with pictures (or flow-charts), but obviously I can't
attempt to reproduce them here.
Anyway, hope this helps.
Pete