a range of values

[Mikest]

Hello All and thanks in advance,

Here's a simply question I guess.
I want the cell D2 to give the value of E21xB2 where if
B2=1to3 give the value of 1.75
B2=4to8 give the value of 0.75
B2=9-20 give the value=0.65
B2>21 give the value of .5

Hope I've explained this question simple enough.
Thanks again
Mike

 

[Mikest]

Sorry here's the example:
E21=1.75
B2=4
So D2=1.75 X 0.75 (0.75 is the result of B2 being 4)
Thanks
Mike

 

[Juan Pablo González]

=E21*VLOOKUP(B2,{-9.999E-307,0;1,1.75;4,0.75;9,0.65;21,0.5},2)

Should work ok.

 

[DBA]

Mike,

Try this

=E21*IF(B2<4,1.75,IF(B2<9,0.75,IF(B2<21,0.65,0.5)))

Regards,

DBA

 

[Aladin Akyurek]

On 2002-08-29 16:24, Juan Pablo G. wrote:
=E21*VLOOKUP(B2,{-9.999E-307,0;1,1.75;4,0.75;9,0.65;21,0.5},2)

Should work ok.

Mike might need that lookup table somewhere else too...
This message was edited by Aladin Akyurek on 2002-08-29 16:31

 

[PaddyD]

Exactly the same method as Juan's, just with the lookup table in some cells rather than in the formula (makes it a bit easier to maintain). Also note that negative numbers will return an error:

Book6
	A	B	C	D
1
2	Table	Ratio
3	1	1.75
4	4	0.75
5	9	0.65
6	21	0.5
7
8
9	B2	E21	D2
10	2	10	17.50
11	8	10	7.50
12	15	10	6.50
13	25	10	5.00
14	-10	10	#N/A
15
Sheet1

paddy

 

[Juan Pablo González]

Ok Aladin... then, put the table in D1:E5 somewhere you can use. And change the VLOOKUP to:

Libro1
	B	C	D	E
1			-9.999E-307	0
2	4		1	1.75
3	0.75		4	0.75
4			9	0.65
5			21	0.5
Hoja2

 

[Mikest]

Brillant,
Thanks for being so quick
PaddyD, Juan, DBA & Aladin