If & Else

[Xirom431]

Hi,

I have these 3 columns. If Column 2 less than 5 then it will be 100% in Column 3 and anything below 5 will be %. I have tried if and else but it did not work. Not very good at these formulas. Thanks. Excel 2010.

<TABLE style="WIDTH: 138pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=183><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=61><TBODY><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 17.1pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22 width=61>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl71 width=61>4</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl72 width=61>1.3</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.0</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>6</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>0.8</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.0</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>7</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>0.7</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>4</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.3</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.0</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.0</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>2.5</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>3</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.7</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>4</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>1.3</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.1pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl70 height=22>5</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl71>8</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl72>0.6</TD></TR></TBODY></TABLE>

 

[njimack]

Your posted sample doesn't make sense - the first row should equal 1, not 1.3. Anyway, try this in C1 and copy down:
=MIN(A1/B1,1)

 

[ganeshpoojary05]

Try this:

=IF(A1>5,1,A1/B1)