If Function

[wilkin2002]

I would like to create an if statement with a number or arguments.

e.g.

if cell c2 is between 15.1 and 16.0 return the value 1

if cell c2 is between 16.1 and 17.0 return the value 2

if cell c2 is between 17.1 and 18.0 return the value 3

etc

How do i do this?

Cheers

 

[VoG]

Try

=LOOKUP(C2,{15.1,16.1,17.1},{1,2,3})

 

[mmmarks]

and try this

=IF(AND(C2>15.1,C2<16),1,IF(AND(C2>16.1,C2<17),2,IF(AND(C2>17.1,C2<18),3)))

 

[bappakarmakar]

Try this,

=IF(AND(C2>=15.1,C2<=16),1,IF(AND(C2>=16.1,C2<=17),2,IF(AND(C2>=17.1,C2<=18),3)))

 

[jhedges]

This is one way to tackle your problem; however, you are limited to a maximum of 7 nested IF statments.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Value</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Product</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">15.10</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">16.00</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">20.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">17.00</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">17.40</TD><TD style="TEXT-ALIGN: center">3</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=IF(AND(C2>=15.1,C2<=16),"1",IF(AND(C2>=16.1,C2<=17),"2",IF(AND(C2>=17.1,C2<=18),"3","")))</TD></TR><TR><TD>D3</TD><TD>=IF(AND(C3>=15.1,C3<=16),"1",IF(AND(C3>=16.1,C3<=17),"2",IF(AND(C3>=17.1,C3<=18),"3","")))</TD></TR><TR><TD>D4</TD><TD>=IF(AND(C4>=15.1,C4<=16),"1",IF(AND(C4>=16.1,C4<=17),"2",IF(AND(C4>=17.1,C4<=18),"3","")))</TD></TR><TR><TD>D5</TD><TD>=IF(AND(C5>=15.1,C5<=16),"1",IF(AND(C5>=16.1,C5<=17),"2",IF(AND(C5>=17.1,C5<=18),"3","")))</TD></TR><TR><TD>D6</TD><TD>=IF(AND(C6>=15.1,C6<=16),"1",IF(AND(C6>=16.1,C6<=17),"2",IF(AND(C6>=17.1,C6<=18),"3","")))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[wilkin2002]

top advice works a treat

cheers