Nested if statement between values

Try

=LOOKUP(A1,{83,100,111},{"Underachieving","Average","Above average"})

Hello Declan, you can use IF functions like this:

=IF(A1< 83,"",IF(A1<= 99, "Underachieving",IF(A1<=110, "Average", IF(A1<=126,"Above Average",""))))

any value less than 83 or greater than 126 will give a blank

Have a look at these three examples:
Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"><COL style="WIDTH: 83px"><COL style="WIDTH: 85px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">92</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>Underachieving</TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold">Lower</TD><TD style="FONT-WEIGHT: bold">Upper</TD><TD style="FONT-WEIGHT: bold">Comment</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">83</TD><TD style="TEXT-ALIGN: right">99</TD><TD>Underachieving</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">110</TD><TD>Average</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">126</TD><TD>Above Average</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=INDEX($C$5:$C$7,MATCH($A$1,$A$5:$A$7,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"><COL style="WIDTH: 83px"><COL style="WIDTH: 85px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">100</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>Average</TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold">Lower</TD><TD style="FONT-WEIGHT: bold">Upper</TD><TD style="FONT-WEIGHT: bold">Comment</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">83</TD><TD style="TEXT-ALIGN: right">99</TD><TD>Underachieving</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">110</TD><TD>Average</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">126</TD><TD>Above Average</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=INDEX($C$5:$C$7,MATCH($A$1,$A$5:$A$7,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"><COL style="WIDTH: 83px"><COL style="WIDTH: 85px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">110</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>Average</TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold">Lower</TD><TD style="FONT-WEIGHT: bold">Upper</TD><TD style="FONT-WEIGHT: bold">Comment</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">83</TD><TD style="TEXT-ALIGN: right">99</TD><TD>Underachieving</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">110</TD><TD>Average</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">126</TD><TD>Above Average</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=INDEX($C$5:$C$7,MATCH($A$1,$A$5:$A$7,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

VoG said:

Try

=LOOKUP(A1,{83,100,111},{"Underachieving","Average","Above average"})

Click to expand...

Hi Peter.

You're a star!! Seems to do the job he wants. Actually, its one of our education boards inspectors who was looking for it. I didn't realise until I went into the office

Just on this. Am I right in saying that the above formaula works by looking at the value in A1 and compairing it to the three values in the first set curly quotes and assigning the corresponding value in the second set of curly quotes?

I assume that the 83,100 and 111 values in the vlookup are in such a way that it knows if the value falls between 83 and 100 it has to assign 'underAchieving' and so on. Is my understanding correct?

Declan

Yes, that is correct. You might want to ensure that values under 83 don't error:

=LOOKUP(A1,{0,83,100,111},{"","Underachieving","Average","Above average"})

Many thanks to all the others who contributed to my help.

I'm building up a spreadsheet with these examples so they can be used with our A-Level ICT classes and for future reference.

I've also been asked to be on standaby tomorrow for a few more questions so I may need to ask more questions

Declan

VoG said:

Yes, that is correct. You might want to ensure that values under 83 don't error:

=LOOKUP(A1,{0,83,100,111},{"","Underachieving","Average","Above average"})

Click to expand...

Hi Peter.

Cheers. I'll modify the formula to include this.

Declan

barry houdini said:

Hello Declan, you can use IF functions like this:

=IF(A1< 83,"",IF(A1<= 99, "Underachieving",IF(A1<=110, "Average", IF(A1<=126,"Above Average",""))))

any value less than 83 or greater than 126 will give a blank

Click to expand...

Cheers. That gives me a number of options to choose from.

Many thanks
Declan

Hi All,

Wondering if someone could help with a similar problem?

I have a data file with date values in column B and time values in column J. I would like to insert a formula into column O that returns the shift which the time value pertains to. The shifts are as follows:

0600-1400: Day Shift
14:00 to 22:00: Back Shift
22:00 to 06:00: Night shift.
Fridays
0600 to 13:00 Day Shift
13:00 to 20:00 Back Shift
Sunday
23:00-0600: Night Shift
All other weekend times: OT.

Probably best to ignore the variances for fridays and weekends at the moment, unless it is easier to encorporate than I imagine?

In any case, it would be great to have some help!

Jon