a range of values

Mikest

New Member
Joined
Mar 5, 2002
Messages
20
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Mikest

New Member
Joined
Mar 5, 2002
Messages
20
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
 

DBA

Board Regular
Joined
May 28, 2002
Messages
100
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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


paddy
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top