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

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
 
Upvote 0
Mike,

Try this

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

Regards,

DBA
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top