Nested IF Then statements

[kcompton]

Hi-
I have a spreadsheet (below) where I am trying to say Yes or No to whether or not a student met their goals.
If a student's At Above/Below Grade Level (column B) is "At Above" and their Spring End RIT column C is equal to or greater than One Year Growth (column D) then the Met Growth Goals (Column F) would = Yes, but if their At Above/Below Grade Level (column B) is "Below" and the Spring End RIT (column C) is Greater than or equal to the Fall RIT SEM (column A) PLUS 1.5 Year Growth ( Column E) then Met Growth Goal (column F) ="No"

I've tried a few options and can't make it work, and it occurs to me maybe it can't be done? Of if it can, can you help"

<style type="text/css">
table.tableizer-table {
font-size: 9px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>







[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]Fall Rit + SEM[/TH]
[TH]At Above /Below Grade Level[/TH]
[TH]Spring End Rit _ SEM[/TH]
[TH]1 year Growth [/TH]
[TH]1.5 Year Growth[/TH]
[TH] Met Growth Goals?[/TH]
[/TR]
</thead><tbody> [TR]
[TD]118.3[/TD]
[TD]Below[/TD]
[TD]151.3[/TD]
[TD]159.1[/TD]
[TD]28.7[/TD]
[TD]=IF (B2="At Above" and C2 >= D2, then F2="Yes"), If (B2="Below" and C2 >= A2+E2 then F2="No"))[/TD]
[/TR]
[TR]
[TD]119.2[/TD]
[TD]Below[/TD]
[TD]138.2[/TD]
[TD]159.1[/TD]
[TD]28.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]141.1[/TD]
[TD]At Above[/TD]
[TD]170.3[/TD]
[TD]159.1[/TD]
[TD]28.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]138.2[/TD]
[TD]Below[/TD]
[TD]147.1[/TD]
[TD]159.1[/TD]
[TD]28.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]150.2[/TD]
[TD]At Above[/TD]
[TD]169.4[/TD]
[TD]159.1[/TD]
[TD]28.7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you

 

[mumps]

Re: Help needed with nested IF Then statements

Try:
=IF(AND(B4="At Above",C4>=D4),"Yes",IF(AND(B4="Below",C4>=A4+E4),"no",""))

 

[kcompton]

Re: Help needed with nested IF Then statements

Thank you!
in the formula, i also need , for example, if B4 = "At Above", and C4<D4 it returns a "NO", and if B4="Below", and C4< A4+E4 it returns a "Yes".
I'm trying for a Yes or No in each cell in the "met Growth Goals" column.
Once again, thank you for your help.

 

[mumps]

Re: Help needed with nested IF Then statements

So is the formula working as you want? What do you mean by "if B4 = "At Above", and C4 I'm trying for a Yes or No"?

 

[kcompton]

Re: Help needed with nested IF Then statements

So is the formula working as you want? What do you mean by "if B4 = "At Above", and C4 I'm trying for a Yes or No"?

 

[kcompton]

Re: Help needed with nested IF Then statements

Sorry if my last reply was odd - I accidently hit "reply with a quote"
Your question:So is the formula working as you want? What do you mean by "if B4 = "At Above", and C4 I'm trying for a Yes or No"?
Answer - Yes, the formula works - and yes, I'm trying for a Yes or a No for each..

Thanks again

 

[kcompton]

Re: Help needed with nested IF Then statements

Would this work? Does it make sense?
<d4),"no",if(and(b4="below",c4>
=IF(AND(B2="At Above",C2>=D2),"Yes",IF(AND(B2="At Above",C2<D2),"No",IF(AND(B2="Below",C2>=A2+E2),"No",IF(AND(B2="Below",C2<A2+E2),"No"))))

</d4),"no",if(and(b4="below",c4>

 

[kcompton]

Re: Help needed with nested IF Then statements

My last reply did not copy the full formula in it:
Here's what I tried...
=IF(AND(B2="At Above",C2>=D2),"Yes",IF(AND(B2="At Above",C2< d2),"no",IF(AND(B2="Below",C2>=A2+E2),"No",IF(AND(B2="Below",C2< a2+e2),"no"))))

 

[Fluff]

Re: Help needed with nested IF Then statements

When using > or < symbols you need to put a space before/after otherwise they are seen as HTML code.

 

[mumps]

Re: Help needed with nested IF Then statements

It looks like if B2="Below" you want "No" to be returned. If that's the case, then try: =IF(AND(B2="At Above",C2>=D2),"Yes","No")