Long IF Statement

[dgavin]

How do i write the followin IF statment.

IF B4 >1.00 and <=2.00 then what is B4 - 0.01, IF B4 >2.00 and <=3.00 then what is B4 - 0.02,IF B4 >3.00 and <=4.00 then what is B4 - 0.05.

Thanks

 

[Andrew Poulsom]

Use a lookup table:

http://www.contextures.com/xlFunctions02.html

 

[mikerickson]

What if B4 <= 1 or 4 < B4?

Something like =B4 - LOOKUP(B4-(0.000000000000001), {1,2,3,4}, {0.1,0.2,0.5})

 

[dgavin]

1.00 – 2.00=0.01
2.00 – 3.00=0.02
3.00 – 4.00=0.05
4.0 – 6.0=0.1
6.0 – 10.0=0.2
10.0 – 20.0=0.5
20 – 30=1
30 – 50=2
50 – 100=5
100 – 1000=10

This i my exact data increments. No data will be below 1 or above 1000

Thanks

 

[markmzz]

Try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Value</TD><TD>Result</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0,01</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0,01</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0,02</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0,02</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0,05</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">0,10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">0,10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">0,20</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">0,20</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">0,50</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">0,50</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">1,00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">1,00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">34</TD><TD style="TEXT-ALIGN: right">2,00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">2,00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right">5,00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">5,00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">10,00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">10,00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">1000</TD><TD style="TEXT-ALIGN: right">10,00</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(A2,$E$1:$F$10,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[BrandonWLH]

Or you could do a select case using a divide/type cast

select case Int(var)
   case 1
   
   case 2
        
   case 3
        
end select

 

[T. Valko]

1.00 – 2.00=0.01
2.00 – 3.00=0.02
3.00 – 4.00=0.05
4.0 – 6.0=0.1
6.0 – 10.0=0.2
10.0 – 20.0=0.5
20 – 30=1
30 – 50=2
50 – 100=5
100 – 1000=10

This i my exact data increments. No data will be below 1 or above 1000

Thanks

Try this...

Book1
	B	C	D	E
4	12	_	1	0.01
5	0.5	_	2	0.02
6	_	_	3	0.05
7	_	_	4	0.1
8	_	_	6	0.2
9	_	_	10	0.5
10	_	_	20	1
11	_	_	30	2
12	_	_	50	5
13	_	_	100	10
Sheet1

Create the 2 column table in D4:E13.

Then, this formula entered in B5:

=LOOKUP(B4,D4:E13)