Nested if statement between values

[dpbarry]

Hi Folks..

Would appreciate a bit of assistance on a nested if statement. My boss has asked if it is possible to format a cell based on the contents of another cell.

What he means is, if we have the following grouping:

83-99 'Underachieving'
100-110 'Average'
111-126 'Above Average'

and say cell A1 has the value 92, cell B2 would say 'Underachieving'. Similarily, if cell A1 had the value 120, Cell B2 would say 'Above Average'
How do I format the nested if statement in Cell B2 to display the relevant value.

Assistance greatly appreciated.

Declan

 

[VoG]

Try

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

 

[barry houdini]

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

 

[JackDanIce]

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>

 

[dpbarry]

Try

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

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

 

[VoG]

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"})

 

[dpbarry]

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

 

[dpbarry]

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"})

Hi Peter.

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

Declan

 

[dpbarry]

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

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

Many thanks
Declan

 

[BSWJON]

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