Explain this formula plz!!

  • Thread starter Thread starter KrisB_bacon
  • Start date Start date
K

KrisB_bacon

Could you explain, in lamens terms, what this formula does, especiall
the AND function.

=IF(D9="No","",IF(AND(C9>=2002,
C9<=2008),VLOOKUP(8,Classes,(C9-2002)*3+5),""))

P.S. the C9>=2002..... is dealing with years 2002 to 2008


Thx

KrisB_baco
 
Hi
in general: to analyze formulas the best way is starting from the
inner-most part:
1. You have an IF function which first checks if cell D9 is equal to
"No". if yes it return "" (or nothing).
2. If cell D9 <>"No" the second part of this function is evaluated.
That is the part
...,IF(AND(...),VLOOKUP(...),""))
3. The AND function combines two conditions. Only if both return TRUE
the following part (that is VLOOKUP) is evaluated. So in your case cell
C9 has to be >2002 AND <=2008)
4. If this AND condition is TRUE the VLOOKUP function is invoked,
otherwise the formula returns ""
5. The VLOOKUP formula searches for the value '8' in rhe range
'Classes' and return the col C9-2002*3+5
 
Hi Kris!

If D9 is No then the formula returns "" (an appearance of a blank
cell)
Otherwise:
If C9 is between 2002 and 2008 (inclusive) it looks up the value 8 in
the lookup table named "Classes" and returns the value in the column
calculated using (C9-2002)*3+5
If C9 isn't in the 2002-2008 range it returns ""

I'd prefer to bunch all my returns of "" in one statement:

=IF(OR(D9="No",AND(C9<2002,C9>2008)),"",VLOOKUP(8,Classes,(C9-2002)*3+
5))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Kris

Hope this is clear.

If D9 equals "No" then show Blank

If C9 is between 2001 and 2009 then
lookup (ref value, Range Classes, etc

To be between 2001 and 2009 both criteria must be met (or
True) so

if C9 is greater than or equal to 2002 AND
C9 is less than or equal to 2008 then
Vlookup(...

Regards
Peter
 
Back
Top