a range of values

Mikest

New Member
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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

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

Should work ok.

Mike,

Try this

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

Regards,

DBA

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

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
ABCD
1
2TableRatio
311.75
440.75
590.65
6210.5
7
8
9B2E21D2
1021017.50
118107.50
1215106.50
1325105.00
14-1010#N/A
15
Sheet1

Ok Aladin... then, put the table in D1:E5 somewhere you can use. And change the VLOOKUP to:
Libro1
BCDE
1-9.999E-3070
2411.75
30.7540.75
490.65
5210.5
Hoja2

Brillant,
Thanks for being so quick

Replies
2
Views
277
Replies
5
Views
400
Replies
2
Views
453
Replies
4
Views
634
Replies
3
Views
448

1,218,923
Messages
6,145,243
Members
450,604
Latest member
ericmwr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back