look up and multiply

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Folks........I know what I want but I don't know how to get it (oposite of
Anarchy in the UK :0)
This is what I am trying to do-
if B3 >5<10 then D3 shows B10*1, if B3=>10<50 then D3 shows B10*2, if
B3=>50<100 then D3 shows B10*3, if B3=>100 then D3 shows B10*4

Any suggestions please?

Thanks
Wibble
 
In D3:

=B10*LOOKUP(B3,{5,10,50,100},{1,2,3,4})

You don't define what happens when B3 < 5, so you'll either want to make sure it
doesn't happen or test for it.
 
Assuming B3 is an integer, perhaps:
=B10*MATCH(B3,{6,10,50,100})

If you don't wish to get #N/A when B3 is less than 6, then
=IF(B3>5, B10*MATCH(B3,{6,10,50,100}), "")
 
Try this paste into D3
=IF(AND(B3>5,B3<10),B10*1,IF(AND(B3>=10,B3<50),B10*2,IF(AND(B3>=50,B3<100),B10*3,IF(B3>=100,B10*4,""))))
 
Just a thought - you could eliminate the AND's from the formula:

=B10*IF(B3>=100,4,IF(B3>=50,3,IF(B3>=10,2,IF(B3>5,1))))
 
Back
Top