IF Help

[WayneGibbs]

Took me a couple of hours to figure this out and there is one option I need to add.

I want to also return a "0" for ANYTHING other than the listed options, but I cant get anything to work.

C=IF(B5=50,"0.29",IF(B5=55,"0.35",IF(B5=60,"0.41",IF(B5=65,"0.47",IF(B5=70,"0.53",IF(B5=35,"0.08",IF(B5=40,"0.16",IF(B5=45,"0.23"))))))))

thanks

 

[schielrn]

I believe it would be:

=IF(B5=50,"0.29",IF(B5=55,"0.35",IF(B5=60,"0.41",IF(B5=65,"0.47",IF(B5=70,"0.53",IF(B5=35,"0.08",IF(B5=40,"0.16",IF(B5=45,"0.23","0"))))))))

But do you really want all these numbers with quotes around them as it is signifying them as text and not actual numbers?

 

[WayneGibbs]

The math is working for me, so far...
what would be more proper?

 

[WayneGibbs]

I believe it would be:

=IF(B5=50,"0.29",IF(B5=55,"0.35",IF(B5=60,"0.41",IF(B5=65,"0.47",IF(B5=70,"0.53",IF(B5=35,"0.08",IF(B5=40,"0.16",IF(B5=45,"0.23","0"))))))))

But do you really want all these numbers with quotes around them as it is signifying them as text and not actual numbers?

This does work, thanks

 

[schielrn]

The math is working for me, so far...
what would be more proper?

I wouldn't put the quotes around them because using certain formulas like sum() will return an error or skip them along with other formulas. See below as all these numbers are entered as text:

Excel Workbook
	A	B
4	.27	.27
5	.44	.44
6	0	0.71
Sheet3

Cell Formulas
Range	Formula
A4	=".27"
A5	=".44"
A6	=SUM(A4:A5)
B4	=".27"
B5	=".44"
B6	=B4+B5

Notice how adding with the + works, but the sum formula does not?

Hope that helps.

 

[T. Valko]

Took me a couple of hours to figure this out and there is one option I need to add.

I want to also return a "0" for ANYTHING other than the listed options, but I cant get anything to work.

C=IF(B5=50,"0.29",IF(B5=55,"0.35",IF(B5=60,"0.41",IF(B5=65,"0.47",IF(B5=70,"0.53",IF(B5=35,"0.08",IF(B5=40,"0.16",IF(B5=45,"0.23"))))))))

thanks

Create this 2 column table:

<TABLE style="WIDTH: 68pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=90 border=0 x:str><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" span=2 width=45><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 id=td_post_2660562 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=45 height=17>35 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=45>0.08 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>40 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.16 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>45 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.23 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>50 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.29 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>55 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.35 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>60 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.41 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>65 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.47 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>70 </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.53 </TD></TR></TBODY></TABLE>

Assume that table is in the range D5:E12.

Then:

=IF(COUNTIF(D5:D12,B5),VLOOKUP(B5,D5:E12,2,0),0)