Hi -

I have used basic formulas in the past but have recently started a new
position in which complicated formulas are used. I was hoping for some basic
help on how to read - or decipher - this formula (and others like it).

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1,"","CHG" )))

or

=IF($O646=0,"NA",IF($O646="SHIPPED",IF($AA646L646 ,"LATE TO
MPS","OT"),IF($O646L646,"LATE TO MPS","OT")))

Thanks Very Much!!
Kelley

Hi,

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(Sheet1!AI$4:AI$2000,AW1388)=1,"","CH G")))

Deciphering the formula is a logical process and I've corrected this one for
the REF# error which is caused by an invalid worksheet reference. I can't
know the name of that sheet so i've used Sheet1.


Break the formula in pieces and evaluation starts form the left and stops as
soon as a condition evaluates as TRUE

IF($A1388="z","",
If A1388 of the current sheet contains a z then the formula stops with "" or
a null string in the cell. Try it and put a z in that cell

IF(AW1388="","N/A"
If there is no z then evaluation continues and looks for a blank cell (null
string) in AW1388. If that's true evaluation stops with N/A in the cell.

IF(COUNTIF(Sheet1!AI$4:AI$2000,AW1388)=1
If that evaluates as false evaluation continues with a countif on sheet1 (or
whatever sheet that was). It counts the instances of the contents of AW1388
in the range AI4 AI2000 and ensures it only appears once and if it does then
the cell is populated with a null string ""


,"CHG")))
The last argument populates the cell with CHG if all the above evaluate as
false.

Mike


"Kell2604" wrote:

Hi -

I have used basic formulas in the past but have recently started a new
position in which complicated formulas are used. I was hoping for some basic
help on how to read - or decipher - this formula (and others like it).

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1,"","CHG" )))

or

=IF($O646=0,"NA",IF($O646="SHIPPED",IF($AA646L646 ,"LATE TO
MPS","OT"),IF($O646L646,"LATE TO MPS","OT")))

Thanks Very Much!!
Kelley

IF is written like this
=IF(Cond1,result_if_cond1_true,result_if_cond1_fal se)

You can replace either true or false result with another IF like this
=IF(Cond1,result_if_cond1_true,=IF(Cond2,result_if _cond2_true,result_if_cond2_false))

and so on
as it has been done in your formula

I think maximum limit is 7 IFs

"Kell2604" wrote:

Hi -

I have used basic formulas in the past but have recently started a new
position in which complicated formulas are used. I was hoping for some basic
help on how to read - or decipher - this formula (and others like it).

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1,"","CHG" )))

or

=IF($O646=0,"NA",IF($O646="SHIPPED",IF($AA646L646 ,"LATE TO
MPS","OT"),IF($O646L646,"LATE TO MPS","OT")))

Thanks Very Much!!
Kelley

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


On Nov 4, 6:59*pm, Kell2604
wrote:
Hi -

I have used basic formulas in the past but have recently started a new
position in which complicated formulas are used. *I was hoping for some basic
help on how to read - or decipher - this formula (and others like it). *

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1*,"","CHG ")))

or

=IF($O646=0,"NA",IF($O646="SHIPPED",IF($AA646L646 ,"LATE TO
MPS","OT"),IF($O646L646,"LATE TO MPS","OT")))

Thanks Very Much!!
Kelley

Thank you all so much - you were a big help! I was reading them all wrong!

"Pete_UK" wrote:

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


On Nov 4, 6:59 pm, Kell2604
wrote:
Hi -

I have used basic formulas in the past but have recently started a new
position in which complicated formulas are used. I was hoping for some basic
help on how to read - or decipher - this formula (and others like it).

=IF($A1388="z","",IF(AW1388="","N/A",IF(COUNTIF(#REF!AI$4:AI$2000,AW1388)=1Â*,"","CH G")))

or

=IF($O646=0,"NA",IF($O646="SHIPPED",IF($AA646L646 ,"LATE TO
MPS","OT"),IF($O646L646,"LATE TO MPS","OT")))

Thanks Very Much!!
Kelley

You're welcome - thanks for feeding back.

Pete

On Nov 4, 8:39*pm, Kell2604
wrote:
Thank you all so much - you were a big help! *I was reading them all wrong!